Error in executing stored procedure:Not a valid identifier

Question

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.

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

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 as varchar(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

 

 

 

 

 

 

 

 

Answer 1

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.

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

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 as varchar(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

 

 

 

 

 

 

 

 

Answer 2

 

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...

Answer 3

 

This is a duplicate post.

 

Please see answer in your other posting.

Answer 4

Use the following satement to execute the SP,

 

Code Snippet

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

 

Answer 5

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

Answer 6

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.

Answer 7

Thanks Mani :-)

 

Now it is working.

There were two problems. One

1)set QUOTED_IDENTIFIER ON should be OFF

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

 

 

Kusuma

Answer 8

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

Answer 9

that resolved, thanks