Call Stored Procedure in SQL Query Window

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

SQL Server Development (2000)

Call Stored Procedure in SQL Query Window

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-07 : 03:37:43

I managed to call the deletepromotion stored procedure and wish to display the @SQL which stored the sql statement fired. This is suppose to be for debugging purposes but dont know how to do this.

Another thing is after i "EXEC deletepromotion" in the query window, i received an error msg below. No doubt, it states that 1 row has been affected, when i check the table it did not delete anything. I have also included the code for my original stored procedure which did not work as well. Thanks

(1 row(s) affected)

Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@PROMOTIONIDS'.

Stored Procedure - Debugging


CREATE PROCEDURE [DELETEPROMOTION]


As

DECLARE @SQL VARCHAR(100), @PROMOTIONIDS VARCHAR (100)

SET @PROMOTIONIDS ='4,5'
SET @SQL="DELETE FROM MSTRPROM
WHERE ',' + @PROMOTIONIDS + ',' LIKE '%,' + CONVERT(VARCHAR(20), PROMOTIONID) + ',%'"

SELECT @SQL

EXEC(@SQL)
GO

Stored Procedure - Original


CREATE PROCEDURE [DELETEPROMOTION]
(
@PROMOTIONIDS VARCHAR (100)
)

As

DELETE FROM MSTRPROM
WHERE ',' + @PROMOTIONIDS + ',' LIKE '%,' + CONVERT(VARCHAR(20), PROMOTIONID) + ',%'

GO

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-07 : 03:47:56

I did this instead and it works for deleting. But i am still unable to do anything for the debugging. Thanks

Stored Procedure - Modified Delete


CREATE PROCEDURE [DELETEPROMOTION]
(
@PROMOTIONIDS VARCHAR (100)
)

As

DELETE FROM MSTRPROM
WHERE PROMOTIONID IN (''+@PROMOTIONIDS+'')
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-07 : 09:46:08

It doesn't work for deleting if you have a csv list in @PROMOTIONIDS
you need

where ',' + @PROMOTIONIDS + ',' like '%,' + convert(varchar(20),PROMOTIONIDS) + ',%'

for your dynamic sql it should be
select @SQL='DELETE FROM MSTRPROM
WHERE PROMOTIONID in '(' + @PROMOTIONIDS + ')'
exec (@sql)

you could also use a function to split the csv string into a table and join to that (probably the best solution
see
http://www.nigelrivett.net/SQLTsql/ParseCSVString.html

It is a single statement so debug won't help you.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-01-07 : 10:21:39

I think this should work - suggestions are in red. The SET NOCOUNT thingie will stop the "1 row(s) processed" messages, which would otherwise probably muck up your application layer!


CREATE PROCEDURE dbo.[DELETEPROMOTION]
As
SET NOCOUNT ON
DECLARE @SQL VARCHAR(100), @PROMOTIONIDS VARCHAR (100)

SET @PROMOTIONIDS ='4,5'
SET @SQL="DELETE FROM MSTRPROM
WHERE ',' + @PROMOTIONIDS + ',' LIKE '%,' + CONVERT(VARCHAR(20), PROMOTIONID) + ',%'"

SELECT @SQL

EXEC(@SQL)

DELETE FROM dbo.MSTRPROM
WHERE ',' + @PROMOTIONIDS + ',' LIKE '%,' + CONVERT(VARCHAR(20), PROMOTIONID) + ',%'

SET NOCOUNT OFF
GO

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-07 : 14:48:11

Thanks for the suggestion guys. Appreciate it! I think i stick to modified delete stored procedure. Anybody have any comments on the downside of it or any way i can improve it, i am at all ears. The below code works great for me btw.

I have also taken into account the "SET NOCOUNT ON" and "SET NOCOUNT OFF" and placed it inside any stored procedure. Please take a look and to see if i placed it correctly.


CREATE PROCEDURE [DELETEPROMOTION]
(
@PROMOTIONIDS VARCHAR (100)
)

As

DELETE FROM MSTRPROM
WHERE PROMOTIONID IN (''+@PROMOTIONIDS+'')
GO


CREATE PROCEDURE [ADDPROMOTION] 
(
@PROMOTIONNAME VARCHAR (100),
@PROMOSTARTDATE DATETIME,
@PROMOENDDATE DATETIME,
@DISCOUNTRATE INT,
@PROMODESC VARCHAR(100),
@ITEMIDS VARCHAR(200)
)

As
SET NOCOUNT ON
Declare @PID INT, @SALEITEMID VARCHAR(10)

-- INSERT the new record
INSERT INTO 
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)
-- Now return the InventoryID of the newly inserted record
SET @PID = SCOPE_IDENTITY()

SELECT @PID

INSERT INTO ITEMPROM
(PROMOTIONID, ITEMID)
SELECT @PID, [Data]
FROM DBO.SPLIT(@ITEMIDS, ',')
SET NOCOUNT OFF
GO

Kristen
Test

22859 Posts

Posted - 2006-01-07 : 14:54:23

"Please take a look and to see if i placed it correctly"

Looks fine. Basically you need to turn it ON before you have performed any operation that will return a resultset, and (for "tidiness" turn it off before you exit the Sproc)

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-08 : 08:54:31

DELETE FROM MSTRPROM
WHERE PROMOTIONID IN (''+@PROMOTIONIDS+'')

this needs to be
exec (DELETE FROM MSTRPROM
WHERE PROMOTIONID IN ('+@PROMOTIONIDS+')')

See my previous post.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 01:47:06

quote:
SET @SQL="DELETE FROM MSTRPROM
WHERE ',' + @PROMOTIONIDS + ',' LIKE '%,' + CONVERT(VARCHAR(20), PROMOTIONID) + ',%'"

SELECT @SQL

Refer this thread where we already told you how to use it with and without Dynamic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=59760

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-09 : 03:19:30

Nigel slipped an apostrophe, but you should do it WITHOUT using dynamic SQL as we've explained.

exec ('DELETE FROM MSTRPROM
WHERE PROMOTIONID IN ('+@PROMOTIONIDS+')')

kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-09 : 04:51:27

okay ... thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources