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
