• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Creating Values in stored procedures

I would like to create values inside stored procedures and assign them to certain variables in the stored procedure.  how would I do that?
1 Solution
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT @Variable = 100

SELECT @Variabl2 = SomeCOlumn
FROM SomeTable
WHERE someCondition
or with aggregations:

select @sumval = sum(somefield) from tablename

or from a function

select @value = dbo.udf_functionname()

get current date

select @date = getdate()

get current user

select @currentuser = suser_sname()
Mark WillsTopic AdvisorCommented:
The one thing missing from the above examples is the DECLARE statement to create the variable in the first place.

To set the value of the variables you can use either SET or SELECT and do have some advantages depending on how / where they are being used. For example, in the code below we could use the SELECT strategy to set a few variables from a select clause. It really depends on whether or not it is truly a scaler variable.


Create Procedure usp_My_Procedure (@param int)
   declare @I int
   declare @J int
   declare @K int
   declare @C char(1)
   declare @D datetime
   set @I = @param
   set @J = @I * 100
   set @I = (select max(number) from master..spt_values where type = 'P')  
   select @K = max(number), @C = max(type) from master..spt_values where type = 'P'
   set @D = getdate() + @param
   select @param as [param], @I as [i], @J as [j], @K as [k], @C as [C], @D as [date]
-- then to test it simply use the exec function and pass the parameter (in this case the value 4)
exec usp_My_Procedure 4

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now