开发者

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).

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜