Problem in Dynamic SP(again) - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Problem in Dynamic SP(again)

Author

Topic

Dajer
Starting Member

22 Posts

Posted - 2009-09-09 : 13:12:25

Hi
again I have a problem in Dynamic SQL.
I have written this storedprocedure but it has an Error:

@docdtl nvarchar(50)=null,
	@facnum nvarchar(50)=null,
	@creditor int=0,
	@debtor int=0,
	@office int
AS
DECLARE @Cmd nvarchar(4000),@Where nvarchar(4000)
set @Where=' where OfficeID='+convert(nvarchar(50),@office)+' and (FacNum='''+@facnum+''''')
UNION
select * from Document where OfficeID='+convert(nvarchar(50),@office)+' and (DocDetails='''+@docdtl+''''')'
set @Cmd='select * from Document'

if Creditor<>0
* @Where=@Where+' UNION select * from Document where OfficeID='+convert(nvarchar(50),@office)+' and (Creditor='+convert(nvarchar(50),@creditor)+')'

if Debtor<>0
* @Where=@Where+' UNION select * from Document where OfficeID='+convert(nvarchar(50),@office)+' and (Debtor='+convert(nvarchar(50),@debtor)+')'

if @Where<>''
set @Cmd=@Cmd+@Where

Exec @Cmd

and the Error is:Inncorrect syntax near @Where for 2 * lines.

Sara Dajer

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-09 : 13:18:28

Instead of EXECing your @cmd, just PRINT it to see what it is you are attempting to EXEC. Once you see the resolved sql statement I'm sure you'll see the problem.

Be One with the Optimizer
TG

Dajer
Starting Member

22 Posts

Posted - 2009-09-09 : 13:23:58

because of the errors I can not save it so I can not print it.

Sara Dajer

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-09 : 13:34:07

You are missing the "@" in front of your parameter names.
And the "*" is not correct syntax. Do you mean to have the "*" be "SELECT" or "SET" instead?


if Creditor<>0
* @Where=@Where+' UNION select * from Document where OfficeID='+convert(nvarchar(50),@office)+' and (Creditor='+convert(nvarchar(50),@creditor)+')'

if Debtor<>0
* @Where=@Where+' UNION select * from Document where OfficeID='+convert(nvarchar(50),@office)+' and (Debtor='+convert(nvarchar(50),@debtor)+')'

Be One with the Optimizer
TG

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-09-09 : 13:36:55

1. you forgot set keyword in those2 lines. you need to add set keyword

set @where = @where + (blah blah)

2. In IF Statements, you didn't keep @ symbol for creditor and debitor

3. it seems to be you didn't initializing @cmd variable?????

Dajer
Starting Member

22 Posts

Posted - 2009-09-09 : 14:52:21

thanx.TG the * at the first of statement is for showing the location of Error.
thank you ddramireddy.I will try it as u said.

Sara Dajer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources