Error in executing stored proceduNot a valid identifier

Kusuma585539

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

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.

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

DaleJ

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

DaleJ

This is a duplicate post.

Please see answer in your other posting.

Manivannan.D.Sekaran

Use the following satement to execute the SP,

Code Snippet

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

hunchback

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

Manivannan.D.Sekaran

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.

Kusuma

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

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