Not a valid identifier while executing an Stored procedure

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

|||

First off, I'm not sure why you're constructing a dynamic select inside your procedure...the procedure should be the select statement, using any input parameters you defined.

But to solve the problem, you need to change

exec @.sqlstr

to

exec(@.sqlstr)

I'd rewrite the entire piece of code...

|||

This is a duplicate post.

Please see answer in your other posting.

|||

Use the following satement to execute the SP,

Code Snippet

Exec [GSU_Site_ReterieveActiveSitesOnSearch]' where GSUStatus.GSUStatusID=1and site.Sitename like''lakshmisite'' '

|||

Kusuma,

Instead passing the this value " where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' ", use:

' where GSUStatus.GSUStatusID=1 and site.Sitename like ''lakshmisite'''

Notice that I am using two apostrophes per each one inside the string.

As you can see, you are setting QUOTED_IDENTIFIER to on, when creating the sp, so anything enclosed by double quote will be interprete as an identifier (name of a column, table, etc.), so when you pass that value to the sp, it will look like

...

SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID +

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and there is not such identifier in your db.

you can set QUOTED_IDENTIFIER to OFF, but I prefer to leave it as ON and use the other method to escape apostrophes.

AMB

|||

If you call it from any UI, the single quote will be automatically taken care by the providers/ADO classes. (since it is a parameter)

But when you test the sp, you have to use either escape sequence or as AMB sujest use the QUOTED_IDENTIFER OFF config.

|||

Thanks Mani :-)

Now it is working.

There were two problems. One

1)setQUOTED_IDENTIFIERON should be OFF

2)exec@.sqlstr should be exec(@.sqlstr)

Kusuma

|||

Hai Dalej,

Sorry for posting two times.

I need dynamic query for a searching -sitenames,Businessunit etc......... ( searching based on columns in a table)

Now the problem is solved by giving exec(@.sqlstr) instead of exec @.sqlstr.

Thanks for your help :-)

Kusuma