Declare Variable in STORED PROCEDURE

Posted by Rajujrk under Sql Server on 3/1/2010 | Views : 106220 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hai All,

How to declare a variable in Stored Procedure in SQL Server 2005,

i want to pass the value from the argument to the local variable and i concat some value with the local variable. the n i pass the local variable to my query..

And also i need how to concat the dynamic variable with the static value...

This is my process in stored procedure...

Thanks & Regards

Rajkumar J

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Vuyiswamb on: 3/1/2010 [Member] [MVP] [Administrator] NotApplicable

0	Good Day Rajujrk let us say you have a Stored Procedure Create Proc sp_ShowMessage as SELECT 'HELLO WORLD' This will show hello world when you run it , but now if you want to pass the variable to the Stored Procedure and Concatinate that Hello world to a string like your name you can do something like this Create Proc sp_ShowMessage ( @Myname varchar(50) ) as SELECT 'HELLO WORLD' +' ' + @Myname Now if you want to execute this , you will need to do this exe sp_ShowMessage 'Vuyiswa' and this will return "Hello world Vuyiswa" is this what you you want ? Thank you for posting at Dotnetfunda Vuyiswa Maseko Thank you for posting at Dotnetfunda [Administrator] Rajujrk, if this helps please login to Mark As Answer. \| Alert Moderator

Good Day Rajujrk

let us say you have a Stored Procedure

Create Proc sp_ShowMessage

as



SELECT 'HELLO WORLD'

This will show hello world when you run it , but now if you want to pass the variable to the Stored Procedure and Concatinate that Hello world to a string like your name you can do something like this

Create Proc sp_ShowMessage

(

@Myname varchar(50)

)

as

SELECT 'HELLO WORLD' +'  ' +  @Myname

Now if you want to execute this , you will need to do this


exe sp_ShowMessage 'Vuyiswa'

and this will return "Hello world Vuyiswa"

is this what you you want ?

Thank you for posting at Dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Rajujrk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Abhi2434 on: 3/1/2010 [Member] [Microsoft_MVP] [MVP] Silver

0	We declare a variable using @ sign in Sql Server. Just like : Declare @v_myVariable INT Set @v_myVariable = 10; You can declare any variable like this. Just make sure you start the variable name with p when its in parameter list of the stored procedure and with v when its a normal local variable. This is what we commonly do. www.abhisheksur.com Rajujrk, if this helps please login to Mark As Answer. \| Alert Moderator

We declare a variable using @ sign in Sql Server.

Just like :

Declare @v_myVariable INT
Set @v_myVariable = 10;

You can declare any variable like this. Just make sure you start the variable name with p when its in parameter list of the stored procedure and with v when its a normal local variable. This is what we commonly do.

www.abhisheksur.com

Rajujrk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Syedshakeer on: 3/3/2010 [Member] Starter

0	Hi, For understanding puporse see the following Link http://msdn.microsoft.com/en-us/library/aa174792(SQL.80).aspx Syed Shakeer Hussain Rajujrk, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts