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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure

Author

Topic

cfb
Starting Member

1 Post

Posted - 2011-06-27 : 08:48:05

Whats wrong with this Store Procedure? I don’t get the values I expect to see in the table marked for ‘Updating’.

If you put the ‘Update’ code into a separate SQL query it will run fine. However the 'Update' code does not work in the Store Procedure and does not return an error.

I have managed to get a work around for the Stored Procedure but would like to know where I am going wrong. If you can spot anything let me know.

Cheers

C
/*********/

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Create date: 27/06/2011
-- Description:
-- =============================================
--CREATE PROCEDURE TAM_Password_ShortMonth
ALTER PROCEDURE [dbo].[spTAM_Password_ShortMonth]

-- Add the parameters for the stored procedure here
--@p1 varchar(10) = T1.Date_Reset,
--@p2 varchar(10) = T2.dteDay
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

/**** Stored Procedure [dbo].[TAM_Password_ShortMonth] Script Date 27/06/2011 ****/

--Did not work in stored procedure. However it works in stand alone query!!
--update T1
--Set chrShortMonth = T2.chrShortMonth
--From BIU.dbo.tblEC_TAMPasswordResets T1
-- JOIN BIU.dbo.tblCalendar T2
-- ON CAST(T1.Date_Reset AS VARCHAR(10)) Like CAST(T2.dteDay as varchar(10))

--Did not work in stored procedure. However it works in stand alone query!!
update tblEC_TAMPasswordResets
SET chrShortMonth = tblCalendar.chrShortMonth
FROM tblCalendar
WHERE CAST(tblEC_TAMPasswordResets.Date_Reset AS VARCHAR(10)) Like CAST(tblCalendar.dteDay as varchar(10))

--SELECT @p1, @p2
END

'Its only bloody obvious when you know how!'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-27 : 09:12:57

I suspect it's because you don't have a style on the convert (use convert with a style not cast).
Also is one of the dates a character column?

You don't have a wild card so that like is actually an =.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources