Problem in Dynamic stored Procedure

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 stored Procedure

Author

Topic

Dajer
Starting Member

22 Posts

Posted - 2009-09-05 : 03:54:27

Hi friends
I write this stored procedure for searching.


        @docdtl nvarchar(50)=null,
	@facnum nvarchar(50)=null,
	@creditor int,
	@debtor int,
	@office int
AS

DECLARE @Cmd nvarchar(4000), @Where nvarchar(4000)
set @Where='Where OfficeID=@office '
set @Cmd='select * from Document '
if NOT @docdtl Is NULL
set @Where='DocDetails ='+@docdtl
if NOT @facnum Is NULL begin
if @Where <>''
set @Where=@Where+' and '
set @Where=@Where+'FacNum ='+@facnum
END
if Not @creditor Is Null Begin
if @Where <>''
set @Where=@Where+' and '
set @Where=@Where+'Creditor ='+@creditor
End
if Not @debtor Is Null Begin
if @Where<>''
set @Where=@Where+' and '
set @Where=@Where+'Debtor ='+@debtor
End
if @Where <>''
set @Cmd=@Cmd+' Where '+@Where
Exec(@Cmd)

but I have this error:

Conversion failed when converting the nvarchar value 'Where OfficeID=@office  and Creditor =' to data type int.

where is my problem?

Sara Dajer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-05 : 04:47:12

print @cmd and see if the statement is correct

Madhivanan

Failing to plan is Planning to fail

Dajer
Starting Member

22 Posts

Posted - 2009-09-05 : 04:53:13

I didn't understand what you mean.
what do u mean by print.I can't see the resault.It has error.

Sara Dajer

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-05 : 05:08:46

quote:
Originally posted by Dajer

I didn't understand what you mean.
what do u mean by print.I can't see the resault.It has error.

Sara Dajer

Before Execute print the varaible

i.e

print @Cmd
-- Exec(@Cmd)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Dajer
Starting Member

22 Posts

Posted - 2009-09-05 : 05:26:09

I did it.The same Error.
what's the problem?

Sara Dajer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-05 : 05:42:56

Post the result of print @Cmd

Madhivanan

Failing to plan is Planning to fail

Dajer
Starting Member

22 Posts

Posted - 2009-09-05 : 06:05:17

This is the resault:

http://shareimage.org/viewer.php?file=bmk580absri6gn7joib.jpg

Sara Dajer