Declare Variable in STORED PROCEDURE

Posted by Rajujrk under Sql Server on 3/1/2010 | Views : 100525 | Status : [Member] | Replies : 3
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



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response