How do I declare variables per row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How do I declare variables per row?

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2014-10-13 : 12:18:33

I know how to set a variable for the entire query, but how do I set one per row?

I have one query which is growing beyond my ability to understand/maintain it. I factor in the sale price of an item based on cost, our markup, shipping & channel fees. The problem is that each one of these has it's own variability. I would like to be able to store inline calculations as a variable such that I can call & add them. Example:

If my code currently looks like this

CREATE TABLE #tempPrice (
product NVARCHAR(40)
,price DECIMAL(18,2)
)

insert into #tempPrice (product,price) values('prod1','18.00')
insert into #tempPrice (product,price) values('prod2','489.00')
insert into #tempPrice (product,price) values('prod3','78.99')


SELECT 
	product
	,price
	,Cast(price * 1.1 as decimal(18,2)) as markup
	,Cast(price * 1.1 as decimal(18,2)) + 5 as 'markup-and-shipping'
FROM #tempPrice

I would like to be able to work with it this way:

SELECT 
	product
	,price
	,Cast(price * 1.1 as decimal(18,2)) as markup
	,@markup + 5 as 'markup-and-shipping' -- <-- This factors in all of the previous calculations, plus something new.
FROM #tempPrice

Is it possible in SQL?

-Sergio
I use Microsoft SQL 2008

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-13 : 12:26:56

You could add your other parameters as columns in the table - for example like this:

CREATE TABLE #tempPrice (
product NVARCHAR(40)
,price DECIMAL(18,2)
,markuprate  float
,shippingcost FLOAT
)

insert into #tempPrice (product,price, markuprate, shippingcost) values('prod1','18.00',1.1,5)
insert into #tempPrice (product,price, markuprate, shippingcost) values('prod2','489.00',1.1,7)
insert into #tempPrice (product,price, markuprate, shippingcost) values('prod3','78.99',1.2,3)


SELECT 
	product
	,price
	,price * markuprate as markup
	,price * markuprate + shippingcost as 'markup-and-shipping'
FROM #tempPrice

SergioM
Posting Yak Master

170 Posts

Posted - 2014-10-13 : 15:01:56

I definitely wouldn't want to store derived information. Or did you mean to store it in a temp table?

I could run a query to import into a temp table & then join that with existing data. Would you say that's the best way to go about it?

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
    DROP TABLE #tempTable

IF OBJECT_ID('tempdb..#tempMarkup') IS NOT NULL
    DROP TABLE #tempMarkup

CREATE TABLE #tempTable (
product NVARCHAR(40)
,price DECIMAL(18,2)
)

insert into #tempTable (product,price) values('prod1','18.00')
insert into #tempTable (product,price) values('prod2','489.00')
insert into #tempTable (product,price) values('prod3','78.99')


SELECT 
product
,price * 1.1 as markup
INTO #tempMarkup
FROM #tempTable


SELECT 
	t.product
	,price
	,m.markup as markup
FROM #tempTable t
INNER JOIN #tempMarkup m
	ON t.product = m.product

-Sergio
I use Microsoft SQL 2008

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 16:06:12

In your original post, where does @markup come from?

SergioM
Posting Yak Master

170 Posts

Posted - 2014-10-13 : 16:27:38

Markup is

Cast(price * 1.1 as decimal(18,2)) as markup

In my second example I mention how I would like to call it, but it was never declared as a variable.

-Sergio
I use Microsoft SQL 2008

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 08:40:09

quote:
Originally posted by SergioM

Markup is
Cast(price * 1.1 as decimal(18,2)) as markup

In my second example I mention how I would like to call it, but it was never declared as a variable.

-Sergio
I use Microsoft SQL 2008

I see, but you never defined or set the @markup variable. That's what I was asking about.

SergioM
Posting Yak Master

170 Posts

Posted - 2014-10-14 : 10:16:31

Correct. The thread was titled 'How do I declare variables per row?' and I went on to illustrate how I wanted to be able to call the variable. In retrospect I should have put them together for greater clarity.

In either case, I've decided to go the temp table route. Calculations happen as separate temporary tables and are grouped together at the end.

-Sergio
I use Microsoft SQL 2008

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 10:22:45

OK -- I guess the short answer to your question is, "You can't!" Variables are scoped by batch (i.e. between GO statements)

I think most folks would use a JOIN not a temp table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources