Help with syntax of an sp call in an Insert

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

Transact-SQL (2005)

Help with syntax of an sp call in an Insert

Author

Topic

jc508
Starting Member

3 Posts

Posted - 2009-02-12 : 01:13:16

Hi,
Can somebody please help with the syntax of calling a stored proc as part of a select from.

Offending code is part of an insert trigger
INSERT INTO RUN_AUDIT_LOG (
AUDIT_ID , AUDIT_YYYYMM , TABLE_NME_TXT ,
EVENT_DTE , LOG_TIMESTAMP_TXT , AUDIT_TYPE_CDE ,
OS_USER_UID , DB_USER_UID ,
APP_USER_UID , PHYS_KEY_1_TXT , PHYS_KEY_2_TXT ,
PHYS_KEY_3_TXT , LOGICAL_KEY_1_TXT , LOGICAL_KEY_2_TXT,
LOGICAL_KEY_3_TXT )
select dbo.GET_NEXT_VAL 'RUNID'
, ((year(CURRENT_TIMESTAMP)*100) + month(CURRENT_TIMESTAMP))
, 'CODES'
, CURRENT_TIMESTAMP
, CONVERT(VARCHAR(40), CURRENT_TIMESTAMP, 121)
, 'A'
, system_user
, current_user
, ISNULL(update_uid, system_user)
, SUBSTRING(code_family_cde,1,120)
, SUBSTRING(code_cde,1,120)
, Null
, SUBSTRING(code_family_cde,1,120)
, SUBSTRING(code_cde,1,120)
, Null
from inserted;

The broken bit is
select dbo.GET_NEXT_VAL 'RUNID'
compile error Msg 4104, The multi-part identifier "dbo.GET_NEXT_VAL" could not be bound.

I have tried
select execute dbo.GET_NEXT_VAL 'RUNID'
compile error 102 Incorrect syntax near (

select dbo.GET_NEXT_VAL ('RUNID')
run time error Msg 4121,
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GET_NEXT_VAL", or the name is ambiguous.

as you might guess I am new to this. If its any help the original Oracle version was simply
select RUNID_SEQ.nextval

The sp dbo.GET_NEXT_VAL ('RUNID') basically does a select and update from a single row table.
I have found plenty of suggestions for calling such a function and storing the result but I really need it to work ina set based update or insert.

Thanks for the help
JC

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-12 : 01:26:48

A stored procedures can not be called as part of a select statement.
You can either create a temp table and load the data from the output of the stored procedure into the temp table, then join to the temp table,

EXample
INSERT @temp(ContactID, FirstName, LastName)
EXEC dbo.sp1 @LastName = 'tom'

or convert the stored procedure into a function which can then be included as part of a select statement.

jc508
Starting Member

3 Posts

Posted - 2009-02-12 : 04:26:55

but isn't it true that functions can't update tables?
so (in the absence of an equivalent to an oracle sequence) I am up the creek without a paddle?

JC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 10:18:16

best thing for you is to create a temporary table and put result of sp in it and then join to it in your query.
Alternatively you could do this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 10:25:20

Why are you not using an IDENTITY in the log table?
Instead of having a procedure calculating the new id to insert?
Have you thought about concurrency?

INSERT	RUN_AUDIT_LOG
	(
		--AUDIT_ID,	Make this column IDENITY!
		AUDIT_YYYYMM,
		TABLE_NME_TXT,
		EVENT_DTE,
		LOG_TIMESTAMP_TXT,
		AUDIT_TYPE_CDE,
		OS_USER_UID,
		DB_USER_UID,
		APP_USER_UID,
		PHYS_KEY_1_TXT,
		PHYS_KEY_2_TXT,
		PHYS_KEY_3_TXT,
		LOGICAL_KEY_1_TXT,
		LOGICAL_KEY_2_TXT,
		LOGICAL_KEY_3_TXT
	)
SELECT	CONVERT(CHAR(6), GETDATE(), 112),
	'CODES',
	GETDATE(),
	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121),
	'A',
	system_user,
	current_user,
	COALESCE(update_uid, system_user),
	SUBSTRING(code_family_cde, 1, 120),
	SUBSTRING(code_cde, 1, 120),
	NULL, 
	SUBSTRING(code_family_cde, 1, 120),
	SUBSTRING(code_cde, 1, 120),
	NULL
FROM	inserted

E 12°55'05.63"
N 56°04'39.26"

jc508
Starting Member

3 Posts

Posted - 2009-02-15 : 23:12:51

Thanks Peso, I was about to start heading down that route. The original intent was to clone it from Oracle as that was seen as having the least impact on the front end (which really supports three or four DBMSs) but I think I can get away with identity because the auditing is never written to from the front end.

What form is a FK that refers to an identity column ? Or even a PK in a child record ?

JC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources