Something cool I did not know - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Something cool I did not know

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-15 : 05:21:43

Don't know if this is the right place to post this but I found something cool that I didn't know was possible:

When you define a stored proc and you want to assign some defaults to a parameter then you can take the result of one of the other parameters!

(whether this is useful or not is up for debate!)

Example


ALTER PROCEDURE [browse2].[uspGetWhoSellsWebsites] (
	@UserLanguage CHAR(2)
	, @UserCountry CHAR(2)
	, @IsReturnQuery BIT
	, @OriginPlace VARCHAR(5) = @UserCountry
	, @DestinationPlace VARCHAR(5) = NULL
	, @IncludeDisabled BIT = 0
....
....

Here, if the call doesn't set @OriginPlace it will take the value passed in for @UserCountry

Again -- probably not useful 95% of the time but.........

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-15 : 05:41:41

Hi Transact Charlie! Thanks for given useful information. :-)

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-15 : 06:01:25

Yes this is possible from version 2008 onwards. You can also use like

declare @value1 int = 887234
declare @value2 int = @value1

select @value1 , @value2

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-12-15 : 11:13:57

The defaults can be chained from one parameter to another.

In the example below, the default for @x1 is assigned to @x2 and that in turn is assigned to @x3.


create procedure #temp
	(
	@x1	int	= 1,
	@x2	int	= @x1,
	@x3	int	= @x2
	)
as
select x1 = @x1, x2 = @x2, x3 = @x3
go

exec #temp
exec #temp 5
exec #temp @x2 = 4
go
drop proc #temp

Results:

x1          x2          x3          
----------- ----------- ----------- 
          1           1           1 

(1 row(s) affected)

x1          x2          x3          
----------- ----------- ----------- 
          5           5           5 

(1 row(s) affected)

x1          x2          x3          
----------- ----------- ----------- 
          1           4           4 

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources