Derived values for sp parameters

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

Derived values for sp parameters

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-06-13 : 12:52:40

I'm trying to use values from sub queries and DATEADD() for sp parameters and I'm getting incorrect syntax errors. This is in SS 2005.

For the sub query I get Incorrect syntax near '('.
For the DATEADD() call I get Incorrect syntax near ','.

What am I doing wrong?

		EXEC my_sp '00001' 
			   ,'0001'
			   ,(SELECT [my_GUIID] FROM [my_table] WHERE [index_id] = 1000 AND [OrderNum] IS NOT NULL) 
			   ,SELECT DATEADD(day, 5, GETDATE())  --date + 5 days

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-13 : 12:58:04

[code]DECLARE @param3 UNIQUEIDENTIFIER, @param4 DATETIME
SELECT @param3=[my_GUIID] FROM [my_table] WHERE [index_id] = 1000 AND [OrderNum] IS NOT NULL
SELECT @param4=DATEADD(DAY, 5, GETDATE()) --date + 5 days

EXEC my_sp '00001'
,'0001'
,@param3
,@param4[/code]

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-06-13 : 13:28:12

Thank you.

I was gently chastised on Friday for declaring local variables instead of nesting expressions in my queries. I guess it is not the same when calling a sp.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-13 : 13:40:09

Feel free to ignore Joe's gentle chastisements.

He doesn't deal with vendor-specific syntax.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-13 : 16:36:49

Parameters are scalar values in T-SQL, the ANSI Standard SQL/PSM and most other proprietary SQL 4GL languages. Also, good SQL programers never use GUIDs in a schema; they are for replication and external use only. Also, getdate() is old Sybase dialect; use the ANSI/ISO CURRENT_TIMEWSTAMP.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-06-13 : 17:36:11

Thanks, I think it is fair to say I am not a good SQL programmer, but in this case the structure is not mine, so if they want a GUID I pass a GUID. I do like the CURRENT_TIMEWSTAMP, though.

Some other interesting things the original designers did was to store all dates as varchar(8) (CCYYMMDD) and time is stored separately as varchar(10). The exception being for created_on and modified_on columns, which are datetime. Most GUID columns are uniqueidentifier, while others are stored as varchar(36).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources