Building SQL strings from variables

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

Building SQL strings from variables

Author

Topic

eglons
Starting Member

15 Posts

Posted - 2006-12-09 : 16:08:56

Disclaimer... I am very new to SQL server!

I am trying to create a stored proc. I am passing in some variables which are used as the "data" side of where clause tests, but I also want to pass in a couple of variables to be the variable side of the where clause test, can it be done?

i.e. select id from table where a=1 and b=2 order by my_order_field

becomes

select id from table where a=@data1 and b=@data2 order by @my_variable_field

and I call the sp with exex sp 1,2,my_order_field

Any clues?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-09 : 16:39:05

Yes your code is in order, except you call your SP with exec sp @data1,@data2


CREATE PROCEDURE [sp]
	(@data1 	[varchar](20),
	 @data2 	[varchar](20)
)

AS 

--Your code goes here
select id from table where a=@data1 and b=@data2 order by @my_variable_field

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-10 : 02:22:17

quote:
--Your code goes here
select id from table where a=@data1 and b=@data2 ~~order by @my_variable_field~~

Order By can not include variable as it's expression, instead you will have to make use of CASE statement there:

ORDER BY
	CASE @MY_VARIABLE_FIELD
		WHEN 'COL1' THEN COL1
		WHEN 'COL2' THEN COL2
		WHEN 'COL3' THEN COL3
		...
	END

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

eglons
Starting Member

15 Posts

Posted - 2006-12-10 : 13:34:37

quote:
Originally posted by harsh_athalye

quote:
--Your code goes here
select id from table where a=@data1 and b=@data2 ~~order by @my_variable_field~~

Order By can not include variable as it's expression, instead you will have to make use of CASE statement there:

ORDER BY
CASE @MY_VARIABLE_FIELD
WHEN 'COL1' THEN COL1
WHEN 'COL2' THEN COL2
WHEN 'COL3' THEN COL3
...
END

Thanks for the reply it looks like this has done the trick, although I'm now stuck on the next piece. I want to pass asc or desc into the stored proc and have the order by asc or desc... I've tried this


select * from product where category_id=@cat and product_id>=@first_id 
ORDER BY 
	CASE @sort_field
		WHEN 'product_price' THEN product_price
		WHEN 'product_title' THEN product_title
		WHEN 'supplier' THEN supplier
	END
	CASE @ascdesc
		WHEN 'asc' THEN asc
		WHEN 'desc' THEN desc
	END

but I get a parse error, what am I doing wrong

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-10 : 16:46:59

First of all, you need a comma between the two CASEs.
Also, with the second CASE, you imply that a column ASC or DESC is present.

Peter Larsson
Helsingborg, Sweden

eglons
Starting Member

15 Posts

Posted - 2006-12-10 : 18:00:25

Hi, I don't want to imply a column is present simply build an order by clause of

order by xxxxx asc
or
order by xxxxx desc

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-11 : 00:50:36

One simple but costly way to solve this is using Dynamic SQL.

Other solution is:

SELECT * FROM PRODUCT WHERE CATEGORY_ID=@CAT AND PRODUCT_ID>=@FIRST_ID 
ORDER BY 
CASE @ASCDESC
	WHEN 'ASC' THEN
		CASE @SORT_FIELD
			WHEN 'PRODUCT_PRICE' THEN PRODUCT_PRICE
			WHEN 'PRODUCT_TITLE' THEN PRODUCT_TITLE
			WHEN 'SUPPLIER' THEN SUPPLIER
                        ELSE '0'
		END
END ASC,
CASE @ASCDESC
	WHEN 'DESC' THEN
		CASE @SORT_FIELD
			WHEN 'PRODUCT_PRICE' THEN PRODUCT_PRICE
			WHEN 'PRODUCT_TITLE' THEN PRODUCT_TITLE
			WHEN 'SUPPLIER' THEN SUPPLIER
                        ELSE '0'
		END
END DESC

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

eglons
Starting Member

15 Posts

Posted - 2006-12-11 : 14:17:18

quote:

SELECT * FROM PRODUCT WHERE CATEGORY_ID=@CAT AND PRODUCT_ID>=@FIRST_ID 
ORDER BY 
CASE @ASCDESC
	WHEN 'ASC' THEN
		CASE @SORT_FIELD
			WHEN 'PRODUCT_PRICE' THEN PRODUCT_PRICE
			WHEN 'PRODUCT_TITLE' THEN PRODUCT_TITLE
			WHEN 'SUPPLIER' THEN SUPPLIER
                        ELSE '0'
		END
END ASC,
CASE @ASCDESC
	WHEN 'DESC' THEN
		CASE @SORT_FIELD
			WHEN 'PRODUCT_PRICE' THEN PRODUCT_PRICE
			WHEN 'PRODUCT_TITLE' THEN PRODUCT_TITLE
			WHEN 'SUPPLIER' THEN SUPPLIER
                        ELSE '0'
		END
END DESC

thanks for the reply... I can't see how this will work as I want to

order by product price asc
order by product_price desc

I've actually solved it using the following, not sure if this is the dynamic sql you are talking about but welcome any advice.


select @search_sql='select * from product where category_id=' + cast(@cat as varchar(3)) + 
' and product_id>=' + cast(@first_id as varchar(10)) +
' ORDER BY ' + @sort_field +
' ' + @ascdesc

execute(@search_sql)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-11 : 22:16:17

Have you atleast tried using my solution? Dynamic sql is not a cost-effective solution.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

eglons
Starting Member

15 Posts

Posted - 2006-12-12 : 18:39:31

quote:
Originally posted by harsh_athalye

Have you atleast tried using my solution? Dynamic sql is not a cost-effective solution.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

I'd couldn't see how it would do the asc, desc... but on closer inspection I now see asc/desc at the end of each case.

I've tried it and it works fine. Many thanks I'll use that if that's OK.

Can you explain the problem or point me in the right direction with dynamic sql

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-13 : 10:37:06

1 Cant you use tow select statements with different orders based on the condition?
If @val='ASC'
Select .. order by ASC
else
Select .. order by DESC

2 More on Dynamic SQL
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources