Dynamic SQL in 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 2000 Forums

SQL Server Development (2000)

Dynamic SQL in Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-25 : 08:23:51

Sita writes "Can some one please tell me what is wrong in the below stored procedure. I am having little difficulty in figuring it out?

ALTER PROCEDURE sprptSuperAssgnTypeLog

@AssgnType numeric,
@StartDate datetime,
@EndDate datetime,
@Location varchar(2)

AS
DECLARE @strSQL VARCHAR(2000)

SET @strSQL = 'SELECT tblMailing.Assgn_TypeID, tblMailing.UserID, tblMailing.Date_Entered
, tblMailing.Date_Recd, tblMailing.Case_Number, tblMailing.Atlas_Number, tblMailing.Is_IVD
, tblSent.Sent_Code_Name, tblMailing.Payor_Last_Name, tblMailing.Payor_First_Name
, tblMailing.Date_Signed, tblMailing.Date_Sent, tblMailing.SentID, tblMailing.Comments
, tblAssgnTypes.Assgn_Type_Name, tblUser.User_location, tblMailing.IsSent_VR, tblMailing.Amount
FROM
tblMailing, tblAssgnTypes, tblSent, tblUser
WHERE
tblMailing.Assgn_TypeID = tblAssgnTypes.Assgn_TypeID AND
tblMailing.SentID = tblSent.SentID AND
tblMailing.UserId = tblUser.UserId AND
tblMailing.Assgn_TypeID= @AssgnType '
IF @AssgnType IN (3,5)
BEGIN
SELECT @strSQL = @strSQL + 'AND tblMailing.Date_Entered BETWEEN ''' + CAST(@StartDate AS varchar) + 'AND ''' + CAST(@EndDate AS varchar) + ''''
END
ELSE
BEGIN
SELECT @strSQL = @strSQL + 'AND tblMailing.Date_Sent BETWEEN ''' + CAST(@StartDate AS varchar) + 'AND ''' + CAST(@EndDate As varchar) + ''''
END

EXEC (@strSQL)"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-25 : 08:25:35

change
CAST(@StartDate AS varchar)
to
CAST(@StartDate AS varchar(20))

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources