What is wrong with my sql_executesql statement

Question

What is wrong with my sql_executesql statement

Frances L

SSCarpal Tunnel

Points: 4727
More actions
October 11, 2006 at 1:00 pm

#173170

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER     PROCEDURE dbo.GetCourseDueList
   @unit    nchar(5)     = NULL,
   @coursename nvarchar(40) = NULL,
   @cc   nvarchar(15) = NULL,
   @fromdate datetime     = NULL,
   @todate    datetime     = NULL,
   @debug     bit          = 0 AS


DECLARE @sql        nvarchar(4000),
        @paramlist nvarchar(4000)
begin
SELECT @sql ='o.[Full Name] ,o.Unit,o.[Unit Desc] ,o.JobCCNo ,o.[EMPLNO] from dbo.OcchEmp o
    left join dbo.tblCurrentWinTrainingLog L on o.[EMPLNO] = L.EmplNO
    where L.EmplNO is null '
IF @unit IS NOT NULL
   SELECT @sql = @sql + ' AND o.Unit = @xUnit'
IF @coursename IS NOT NULL
SELECT @sql = @sql + ' AND o.coursename <= @xcoursename'
IF @cc IS NOT NULL
   SELECT @sql = @sql + ' AND o.JObCCNO >= @xcc'

IF @fromdate IS NOT NULL
   SELECT @sql = @sql + ' AND l.CTDate >= @xfromdate'

IF @todate IS NOT NULL
   SELECT @sql = @sql + ' AND l.CTDate <= @xtodate'


SELECT @sql = @sql

IF @debug = 1
   PRINT @sql

SELECT @paramlist = '@xunit   nchar(5),
                     @xcoursename   nvarchar(50),
                     @xcc      nvarchar(15),
                     @xfromdate datetime,
                     @xtodate    datetime'


EXEC sp_executesql @sql, @paramlist,
                   @unit, @coursename, @cc, @fromdate, @todate

end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
EXEC GetCourseDueList

i write my sp_executedsql according to this link. syntax check did not have error. but if I run
EXEC GetCourseDueList

it gave me error on line 5. which is
DECLARE @fromdate datetime
EXEC GetCourseDueList
http://www.sommarskog.se/dyn-search.html

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

Just a couple of guesses you might want to try:

First, I think since you are using nvarchar datatypes, you need to use Unicode strings. Prefix your string constants with "N:, like

SELECT @sql = N'o.[Full Name] ...

Second, check the result of your Print @sql statement, and make sure you have enough of quote marks around your string constants. It looks like you are missing some, but I can't be sure without testing (and I'm not going to do your testing for you).

Hope this helps

Mark

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

1. Your parameters that u are using does not match what's being passed (Eg:@unit and @xUnit).

2. There is No Select in yout SQL.

SELECT @sql ='o.[Full Name] ,o.Unit,o.[Unit Desc] ,o.JobCCNo ,o.[EMPLNO] from dbo.OcchEmp o

left join dbo.tblCurrentWinTrainingLog L on o.[EMPLNO] = L.EmplNO

where L.EmplNO is null '

needs to be

SELECT @sql ='Select o.[Full Name] ,o.Unit,o.[Unit Desc] ,o.JobCCNo ,o.[EMPLNO] from dbo.OcchEmp o

left join dbo.tblCurrentWinTrainingLog L on o.[EMPLNO] = L.EmplNO

where L.EmplNO is null '

Here is how you build SQL:

SELECT @sql ='Select o.[Full Name] ,o.Unit,o.[Unit Desc] ,o.JobCCNo ,o.[EMPLNO] from dbo.OcchEmp o

left join dbo.tblCurrentWinTrainingLog L on o.[EMPLNO] = L.EmplNO

where L.EmplNO is null '

IF @unit IS NOT NULL

SELECT @sql = @sql + ' AND o.Unit = ''' + ltrim(rtrim(@Unit)) +''''

select @sql

Check the SQL and Run it manually and see if it works.

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 3

Frances L

SSCarpal Tunnel

Points: 4727

October 13, 2006 at 7:10 am

#665718

thanks. It worked after I put the select there.