Determine a cursor by condition
In SQL Server for CURSOR we say:
CREATE PROCEDURE SP_MY_PROC
(@BANKID VARCHAR(6)='')
-------------------------------
-------------------------------
DECLARE MY_CURSOR CURSOR FOR
SELECT .......
Now, what I wonder, can we determine the select statement according to a cerain condition?
IF BANKID开发者_开发百科<>''// SELECT * FROM EMPLOYESS WHERE BANKID=@BANKID to be the cursors query
ELSE // otherwise SELECT * FROM EMPLOYEES to be the cursors query
Or does it have to be static?
Yes, you can do this with Dynamic SQL
IF @BANKID<> ''
SET @sql = '
DECLARE MyCursor CURSOR FOR
SELECT ...'
ELSE
SET @sql = '
DECLARE MyCursor CURSOR FOR
SELECT ...'
EXEC sp_executesql @sql
OPEN MyCursor
If it is such a simple example, it's better to re-write it as a single query:
DECLARE MY_CURSOR CURSOR FOR
SELECT * FROM EMPLOYESS WHERE BANKID=@BANKID or @BANKID=''
And, of course, we haven't addressed whether a cursor is the right solution for the larger problem or not (cursors are frequently misused by people not used to thinking of set based solutions, which is what SQL is good at).
PS - avoid prefixing your stored procedures with sp_
- These names are "reserved" for SQL Server, and should be avoided to prevent future incompatibilities (and ignoring, for now, that it's also slower to access stored procs with such names, since SQL Server searches the master database before searching in the current database).
精彩评论