Using a Stored Procedure and a Dynamic WhereClause to pass back strings to my web server

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)

Using a Stored Procedure and a Dynamic WhereClause to pass back strings to my web server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-09-05 : 23:51:23

Amrit writes "I have read your notes on getting to write dynamic SQL statements using Exec and passing the IN conditions as a parameter. However, I am still unclear on one thing, which I believe should be pretty simple:
So I've written the dynamic SQL Squery and now I need to add the data from the rows I need back to my VB script. I previously did this with a Sproc by declaring and using a cursor, however using Exec doesn't seem to me use the cursor (I am using the cursor to write to a string each row is separated by a comma). If I'm not making myself clear, here is my old Sproc:

CREATE PROCEDURE usp_viewProducts
(
@idCategory int,
@arrayIDProduct nvarchar(4000) out,
@arrayLongName nvarchar(4000) out,
@arrayListImage nvarchar(4000) out
)
AS

--DECLARE PRODUCT VARIABLES

DECLARE @idProduct int
DECLARE @LongName nvarchar(50)
DECLARE @ListImage nvarchar(50)

--INITIALISE VARIABLES

SET @arrayIDProduct = '' --initialise idProduct array
SET @arrayLongName = '' --initialise LongName array
SET @arrayListImage = '' --initialise ListImage array

DECLARE product_cursor CURSOR FOR --create cursor (equiv of recordset) to store details of every basket submitted by user
SELECT tblProduct.idProduct, tblProduct.LongName, tblProduct.ListImage FROM tblProduct
WHERE tblProduct.idCategory = @idCategory AND tblProduct.active=1
ORDER BY tblProduct.LongName
Open product_cursor
FETCH NEXT FROM product_cursor INTO @idProduct, @LongName, @ListImage --Move to first
IF @@FETCH_STATUS = 0 --If there are products (IF NOT RS.EOF)
BEGIN
WHILE @@FETCH_STATUS = 0 --While there are products (WHILE NOT RS.EOF)
BEGIN
--Update arrays with data from products
--Note : CAST is used for converting data types. Here it is being used to convert variables to nvarchar
SET @arrayIDProduct = @arrayIDProduct + CAST(@idProduct AS nvarchar(6)) + ','
SET @arrayLongName = @arrayLongName + CAST(@LongName AS nvarchar(50)) + ','
SET @arrayListImage = @arrayListImage + CAST(@ListImage AS nvarchar(50)) + ','
FETCH NEXT FROM product_cursor INTO @idProduct, @LongName, @ListImage --Move to first
END
END
CLOSE product_cursor --Close product cursor
DEALLOCATE product_cursor

...And all I am after is some way to make the SQL statement dynamic, so it can pass back data. Any ideas?

regards,

Amrit"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources