Solved

Creating Values in stored procedures

Posted on 2009-07-13
3
214 Views
Last Modified: 2012-05-07
I would like to create values inside stored procedures and assign them to certain variables in the stored procedure.  how would I do that?
0
Comment
Question by:VBBRett
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24843096
SELECT @Variable = 100

SELECT @Variabl2 = SomeCOlumn
FROM SomeTable
WHERE someCondition
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24845993
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()
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 25014100
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.

e.g.



Create Procedure usp_My_Procedure (@param int)

as

Begin

   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]
 

end

GO
 

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

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Another way of doing this SQL 8 40
Need some help wiht :CAST AS Double 11 38
SQL help 5 48
Not selecting duplicate data 6 31
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now