doubt in my 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

doubt in my stored procedure

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 14:03:29

alter procedure dbo.[sp_update]
(
@login_name varchar(50)
)
as
begin
select top 1 delivery_date from tbl_master_product where client_name=@login_name
end
begin
if(delivery_date!=getdate())
begin
return 1
end
else
begin
return 0
end
end

I have error
Msg 207, Level 16, State 1, Procedure sp_update, Line 10
Invalid column name 'delivery_date'.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 14:04:23

Then how to use the selected value for checking process

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:06:10

Do not use sp_ as the prefix for a stored procedure. There is a performance hit there.

You have to put it into a variable in order to use it.

create procedure dbo.Update
(@login_name varchar(50))
as
declare @v1 datetime
select @v1 = top 1 delivery_date from tbl_master_product where client_name=@login_name
if(@v1<>getdate())
return 1
else
return 0

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 14:09:34

Tnx for response,I tried this but i received this error
Msg 156, Level 15, State 1, Procedure Update, Line 5
Incorrect syntax near the keyword 'top'.
Msg 102, Level 15, State 1, Procedure Update, Line 11
Incorrect syntax near '0'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:13:51

Sorry, here you go:

create procedure dbo.Update
(@login_name varchar(50))
as
declare @v1 datetime
select top 1 @v1 = delivery_date from tbl_master_product where client_name=@login_name
if @v1<>getdate()
return 1
else
return 0
go

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:15:01

Are you sure you want to use RETURN? Why not use output parameters instead? RETURN is typically used for success/failure of the stored procedure. It doesn't have to be, it's just typical.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 14:20:20

One small doubt .. What is the use for <> this symbol..

I need to check only the current date present or not .. If not present I will update it..So only I am using return statement.. Tnx for the nice help..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:22:06

<> means not equal.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 14:23:28

Thank you for your help..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 14:26:00

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-23 : 14:29:30

and returning a value is NOT a good idea...unless M$ changed it...the return value has the potential to be overlayed by SQL Server itself

And TOP without an ORDER BY is meaningless

What do you really want to know?

In this Case it seems like if ANYTHING EXISTS for the login from the mast product table means "TRUE"

Is that what you want?


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbo.usp_SEL_delivery_date]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dbo.usp_SEL_delivery_date]
GO

CREATE PROC dbo.usp_SEL_delivery_date
	  @login_name		varchar(50)
	, @delivery_date	datetime		OUTPUT
	, @rc int OUTPUT, @Error int OUTPUT, @Error int OUTPUT,  @Error_Message varchar(255) OUTPUT
AS

SET NOCOUNT ON

DECLARE @Error_Type int

  SELECT @delivery_date = TOP 1 delivery_date 
    FROM tbl_master_product 
   WHERE client_name=@login_name
ORDER BY delivery_date DESC

SELECT @Error = @@ERROR, @Rowcount = @@ROWCOUNT 

IF @error <> 0
	BEGIN 
	  SELECT @Error_Type = 50001, @rc = -1 
	  GOTO usp_SEL_delivery_date_Err 
	END

IF @rowcount = 0 
	BEGIN 
	  SELECT @Error_Type = 50002, @rc = 1 
		   , @Error_Message = 'Login: "' + @login_name + '" returned 0 Rows'
			   GOTO usp_SEL_delivery_date_Err 
	END

usp_SEL_delivery_date_Err_Exit:

SET NOCOUNT OFF	
RETURN

usp_SEL_delivery_date_Err:
  ROLLBACK TRAN
  IF @Error_Type < 50000 
    RAISERROR @Error_Type @Error_Message
  GOTO usp_INS_SERVICE_INFO_BY_SERVICE_SUB_GROUP_Exit
GO

GRANT EXEC ON [usp_SEL_delivery_date] TO [Some_Role]
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 14:40:06

Nice thought ...I learned some more good ideas from your post.. Tnx for it

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 14:41:34

When executing the above procedure i received the following errors

Msg 134, Level 15, State 1, Procedure usp_SEL_delivery_date, Line 5
The variable name '@Error' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 156, Level 15, State 1, Procedure usp_SEL_delivery_date, Line 12
Incorrect syntax near the keyword 'TOP'.
Msg 137, Level 15, State 1, Procedure usp_SEL_delivery_date, Line 17
Must declare the scalar variable "@Rowcount".
Msg 137, Level 15, State 2, Procedure usp_SEL_delivery_date, Line 25
Must declare the scalar variable "@rowcount".
Msg 137, Level 15, State 1, Procedure usp_SEL_delivery_date, Line 28
Must declare the scalar variable "@Error_Message".
Msg 137, Level 15, State 2, Procedure usp_SEL_delivery_date, Line 40
Must declare the scalar variable "@Error_Message".

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 14:45:00

quote:
Originally posted by X002548

usp_SEL_delivery_date_Err:
ROLLBACK TRAN
IF @Error_Type < 50000
RAISERROR @Error_Type @Error_Message
GOTO usp_INS_SERVICE_INFO_BY_SERVICE_SUB_GROUP_Exit
GO

GRANT EXEC ON [usp_SEL_delivery_date] TO [Some_Role]
GO

What is the use for some_role

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-23 : 16:05:28

My bad

get rid of the red stuff

Some_Role

That would happen to be a role that you SHOULD Create

You then put users into that role when you want them to have access to the sproc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-23 : 16:29:09

and add the blue..I do not know what wrong withj these posting..I edit stuff and it look like the post from before

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 18:28:46

tnx for response

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-23 : 18:40:10

Just an FYI, it's good practice to use SET to assign your variables to avoid unexpected results from a SELECT that returns more than more row. (obviously that would be a logical error, but they can be the hardest to find). For example:

DECLARE @Foo INT
DECLARE @Bar TABLE (ID INT, Val INT)

INSERT @Bar (ID, Val)
VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 2)

-- Succeeds
SELECT @Foo = ID FROM @Bar WHERE Val = 1
SELECT @Foo

-- Fails
SET @Foo = (SELECT ID FROM @Bar WHERE Val = 2)
SELECT @Foo

The second method will fail, so you know that you are returning more than one one tuple and not assigning a random row (like the first assignment).

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-23 : 18:46:39

I got it thank you..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources