Building sql and passing parameters

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)

Building sql and passing parameters

Author

Topic

rett
Starting Member

35 Posts

Posted - 2004-10-04 : 19:58:56

I am building a sql statement. The statement is running but does look like my parameters are being called correct.

DECLARE @PISSUE1 nvarchar(70)
DECLARE @PISSUE2 nvarchar(70)

DECLARE @CNTISSUE1 int
DECLARE @CNTISSUE2 int
DECLARE @SQL nvarchar(2000)

SET @PISSUE1 = '20030331,20020331'
SET @PISSUE2 = '20010331'

***************************************************************
*This part of the logic is counting the number of dates entered
***************************************************************
select @CNTISSUE1 = len(@PISSUE1)-len(replace(@PISSUE1,',','')) + 1

*************************************************
*This is part of the statement that I am building
*************************************************
SET @SQL = '(WHERE select count( distinct ISSUE_ID )'
+ ' from dbo.Orders b where a.name_id = b.name_id'

+ ' AND ISSUE_ID IN (' + @PISSUE1 + ')'

******************************************************************
*I am converting a int to a nvarchar in order for the statement to *work.
******************************************************************
+ ') = ' + CONVERT(nvarchar(4),@CNTISSUE1) + ' AND '

+ 'a.name_id NOT IN (select name_id from dbo.Orders where issue_id=' + @PISSUE2 + ')'

*********************************
*This part kick off the statement
*********************************
SELECT DISTINCT NAME_ID,
V_FIRST_NAME,
V_LAST_NAME,
V_LISTING_NAME,
V_NAME_TYPE,
V_ADDRESS1,
V_ADDRESS2,
V_ADDRESS3,
V_ADDRESS4,
V_CITY,
V_REGION,
V_ZIP_CODE,
V_COUNTRY,
V_AREA_CODE,
V_PHONE_NO
*********************************************
*This is were I think I am having my problems
*********************************************
FROM dbo.Orders as a
SELECT @SQL <--Start selecting data

********************************************
*The statement works when it looks like this
********************************************

SET @PISSUE1 = '20030331'
SET @PISSUE2 = '20020331'
SET @PISSUE3 = '20010331'

FROM dbo.Orders as a
WHERE
(
select count( distinct ISSUE_ID )
from dbo.Orders b where a.name_id = b.name_id

AND ISSUE_ID IN(@PISSUE1,@PISSUE2)
) = @CNTISSUE -- 3 is the number of dates
AND
a.name_id NOT IN (select name_id from dbo.Orders where issue_id=@PISSUE3)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-04 : 20:07:06

What is your question?

Tara

rett
Starting Member

35 Posts

Posted - 2004-10-04 : 20:17:36

I am sorry.

My question is should I use "SELECT (@SQL) or EXEC "(@SQL)" at the end of the FROM in order for the statement to run correctly.

slacker
Posting Yak Master

115 Posts

Posted - 2004-10-04 : 23:09:13

hi rett.. you need to build the entire sql statement into your @sql variable then use exec or sp_executesql.

You cannot execute a partial hardcoded sql statement mixed with dynamic.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-05 : 12:20:06

SELECT @SQL just prints out what the statement is for debugging in Query Analyzer. EXEC (@SQL) or sp_executesql @SQL should be used to execute a dynamic statement.

Tara

rett
Starting Member

35 Posts

Posted - 2004-10-05 : 14:22:42

Thanks you guys have made my day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources