Build dynamic select statement in a sproc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Build dynamic select statement in a sproc

Author

Topic

cygnusX
Starting Member

4 Posts

Posted - 2001-12-03 : 11:37:56

How does one concantenate a sql statement in a sproc? Here is my stored procedure and it is not compiling correctly.

--*****************************************************************
Create Procedure spSearch_SPDTitle
(
@SiteID int,
@PayTypeID int,
@Array varchar(100),
@separator char(1)
)
AS
SET NOCOUNT ON
DECLARE @separator_position int
DECLARE @array_value varchar(100)
DECLARE @sql varchar(1000)

SET @sql = 'SELECT tblSPD.SPDID, tblSPD.Title, tblSPDToPaytype.PayTypeID FROM tblSPD INNER JOIN tblSPDToPaytype ON tblSPD.SPDID = tblSPDToPaytype.SPDID WHERE tblSPD.SiteID = @SiteID AND tblSPDToPaytype.PayTypeID = @PayTypeID AND '

SET @array = @array + @separator

WHILE patindex('%' + @separator + '%' , @array) <> 0
BEGIN
SELECT @separator_position = PatIndex('%' + @separator + '%' , @array)
SELECT @array_value = Left(@array, @separator_position - 1)

--HERE IS THE PART I NEED HELP WITH START
@sql = @sql + 'tblSPD.Title LIKE ''%' + @array_value + '%'' AND '
-- HERE IS THE PART I NEED HELP WITH END

SELECT @array = Stuff(@array, 1, @separator_position, '')
END
SET NOCOUNT OFF

-- GET RID OF LAST 'AND '
@sql = Left(@sql,Len(@sql) - 5)

EXEC(@sql)

GO
--*****************************************************************

I need to go through all the strings in the array passed in @Array and add them to my AND statement.

Thanks, JG

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-03 : 12:00:28

you have something like something like

'a,b,c,d'

and you want

tblSPD.Title LIKE '%a%' and tblSPD.Title LIKE '%b%' and tblSPD.Title LIKE '%c%' and tblSPD.Title LIKE '%d%' and

looks like you can do a replace on @separator with %' and tblSPD.Title LIKE '%
then add and tblSPD.Title LIKE '% to the beginning and %' to the end

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

cygnusX
Starting Member

4 Posts

Posted - 2001-12-03 : 12:08:36

Thanks for the response...it helps some... :)
I think my question actually lies in the part that says "@sql = @sql + 'tblSPD.Title LIKE ''%' + @array_value + '%'' AND '" I want to built that up in a loop then execute it at the end.

I set the first part of the sql statement at the top...then I want to add (with a loop) the AND parts. These parts come from an array that is passed into the sproc.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-03 : 12:20:44

You have left out the SET statement at the beginning of the line.

SET @SQL = @SQL + ...

-------------------
It's a SQL thing...

cygnusX
Starting Member

4 Posts

Posted - 2001-12-03 : 12:33:34

OHH....man.... <sheepish grin> OK..Fixed it now I get the following error:
--**************************************************************
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@SiteID'.
--**************************************************************
SPROC is as follows:
--**************************************************************
CREATE PROCEDURE spSearch_SPDTitle
@SiteID int,
@PayTypeID int,
@Array varchar(100),
@separator char(1)
AS
SET NOCOUNT ON
DECLARE @separator_position int
DECLARE @array_value varchar(100)
DECLARE @sqlStatement varchar(1000)

SET @sqlStatement = 'SELECT tblSPD.SPDID, tblSPD.Title, tblSPDToPaytype.PayTypeID FROM tblSPD INNER JOIN tblSPDToPaytype ON tblSPD.SPDID = tblSPDToPaytype.SPDID WHERE tblSPD.SiteID = @SiteID AND tblSPDToPaytype.PayTypeID = @PayTypeID '

SET @array = @array + @separator

WHILE PatIndex('%' + @separator + '%' , @array) <> 0
BEGIN
SELECT @separator_position = PatIndex('%' + @separator + '%' , @array)
SELECT @array_value = Left(@array, @separator_position - 1)

SET @sqlStatement = @sqlStatement + ' AND tblSPD.Title LIKE ''%' + @array_value + '''%'

SELECT @array = Stuff(@array, 1, @separator_position, '')
END
SET NOCOUNT OFF

EXEC(@sqlStatement)

GO
--**************************************************************
Any ideas out there?

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2001-12-03 : 14:25:49

Your variables are out of scope when you exec your dynamic sql statement.

Instead of this...

SET @sqlStatement = 'SELECT tblSPD.SPDID, tblSPD.Title, tblSPDToPaytype.PayTypeID FROM tblSPD INNER JOIN tblSPDToPaytype ON tblSPD.SPDID = tblSPDToPaytype.SPDID WHERE tblSPD.SiteID = @SiteID AND tblSPDToPaytype.PayTypeID = @PayTypeID '

Do this...

SET @sqlStatement = 'SELECT tblSPD.SPDID, tblSPD.Title, tblSPDToPaytype.PayTypeID FROM tblSPD INNER JOIN tblSPDToPaytype ON tblSPD.SPDID = tblSPDToPaytype.SPDID WHERE tblSPD.SiteID = ' + @SiteID + ' AND tblSPDToPaytype.PayTypeID = ' + @PayTypeID

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 12/03/2001 14:26:24

cygnusX
Starting Member

4 Posts

Posted - 2001-12-04 : 10:15:20

Thank you all... That did it. It works great and is fast. Now onto regular expressions...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources