Error in executing stored procedure:not a valid identifier
-
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' "
-
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' "
Try:
exec(@sqlstr) -
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' "