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

Transact-SQL (2005)

PASSING VARIABLES

Author

Topic

tjl0079
Starting Member

10 Posts

Posted - 2009-02-25 : 17:23:13

IM trying to pass variables in a stored procedure while using a linked server query from oracle the stored procedure pulls back data with the variable

ALTER PROCEDURE [dbo].[CTMS]
)
@ID_NUMBER varchar (50)
)

as

SELECT *
FROM OPENQUERY(CLFYRP, '
SELECT SA.TABLE_CASE.TITLE, SA.TABLE_CASE.ID_NUMBER, SA.TABLE_CASE.OBJID, SA.TABLE_CASE.CREATION_TIME,
SA.TABLE_ACT_ENTRY.ADDNL_INFO, SA.TABLE_ACT_ENTRY.ENTRY_TIME, SA.TABLE_ACT_ENTRY.ACT_ENTRY2CASE
FROM SA.TABLE_CASE ,SA.TABLE_ACT_ENTRY

WHERE SA.TABLE_CASE.OBJID = SA.TABLE_ACT_ENTRY.ACT_ENTRY2CASE AND

(ID_NUMBER) = ''@ID_NUMBER''')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-25 : 17:25:20

ID_NUMBER = ' + @ID_NUMBER + ')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tjl0079
Starting Member

10 Posts

Posted - 2009-02-25 : 17:30:27

Still getting the same results , its not passing the variable

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-25 : 17:31:05

Well you haven't told us what is happening or asked a question, I was just assuming something. Please provide more info!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tjl0079
Starting Member

10 Posts

Posted - 2009-02-25 : 17:33:53

when i execute the query, no data pulls back , when i run the stored procedure with a specified number in the where clause instead of a variable it works

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 17:36:10

Then read this:
http://support.microsoft.com/kb/314520

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-25 : 17:36:26

What is the data type of ID_NUMBER in the table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tjl0079
Starting Member

10 Posts

Posted - 2009-02-25 : 17:41:37

its a varchar2 (20 Byte)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-25 : 17:44:13

Try this:

ID_NUMBER = ''' + @ID_NUMBER + '''')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tjl0079
Starting Member

10 Posts

Posted - 2009-02-25 : 17:47:39

Msg 102, Level 15, State 1, Procedure CTMS, Line 27
Incorrect syntax near '+'.

thats the error im getting when i use ''' + @ID_NUMBER + ''''

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 17:52:43

quote:
Originally posted by tjl0079

Msg 102, Level 15, State 1, Procedure CTMS, Line 27
Incorrect syntax near '+'.

thats the error im getting when i use ''' + @ID_NUMBER + ''''

You are using Oracle right.

tjl0079
Starting Member

10 Posts

Posted - 2009-02-25 : 17:56:24

im using SQL 2005 and using Oracle as a linked server

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 17:58:00

quote:
Originally posted by tjl0079

im using SQL 2005 and using Oracle as a linked server

Did you read the link I gave?

tjl0079
Starting Member

10 Posts

Posted - 2009-02-25 : 18:00:13

i did but im not passing a pre-defined variable im passing a
user-defined variable , on their im only seeing example of pre-defined

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-25 : 18:09:43

It doesn't matter if it's pre-defined or not, a variable is a variable. Try using the method in that link and let us know how it goes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tjl0079
Starting Member

10 Posts

Posted - 2009-02-25 : 18:25:03

ALTER PROCEDURE [dbo].[CTMS]

DECLARE @TSQL varchar(8000),@ID_NUMBER varchar (50)
SELECT @ID_NUMBER ='18054210-090129'

as

SELECT *
FROM OPENQUERY(CLFYRP, '
SELECT @TSQL = SA.TABLE_CASE.TITLE, SA.TABLE_CASE.ID_NUMBER, SA.TABLE_CASE.OBJID, SA.TABLE_CASE.CREATION_TIME,
SA.TABLE_ACT_ENTRY.ADDNL_INFO, SA.TABLE_ACT_ENTRY.ENTRY_TIME, SA.TABLE_ACT_ENTRY.ACT_ENTRY2CASE
FROM SA.TABLE_CASE ,SA.TABLE_ACT_ENTRY

WHERE SA.TABLE_CASE.OBJID = SA.TABLE_ACT_ENTRY.ACT_ENTRY2CASE AND

ID_NUMBER = ''''' + @ID_NUMBER+ ''''''')'

EXEC (@TSQL)

It errored out with this

Msg 156, Level 15, State 1, Procedure CTMS, Line 13
Incorrect syntax near the keyword 'DECLARE'.
Msg 156, Level 15, State 1, Procedure CTMS, Line 18
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Procedure CTMS, Line 29
Incorrect syntax near '+'.

Im running out of solutions, im thinking can this be even done

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-25 : 18:30:44

You aren't creating the stored procedure correctly. Move the DECLARE/SELECT after AS.

You are also jumping too far ahead, we first need to get @TSQL right. Start with this:

DECLARE @TSQL varchar(8000),@ID_NUMBER varchar (50)
SELECT @ID_NUMBER ='18054210-090129'

SET @TSQL = 'SELECT SA.TABLE_CASE.TITLE, SA.TABLE_CASE.ID_NUMBER, SA.TABLE_CASE.OBJID, SA.TABLE_CASE.CREATION_TIME,
SA.TABLE_ACT_ENTRY.ADDNL_INFO, SA.TABLE_ACT_ENTRY.ENTRY_TIME, SA.TABLE_ACT_ENTRY.ACT_ENTRY2CASE
FROM SA.TABLE_CASE ,SA.TABLE_ACT_ENTRY
WHERE SA.TABLE_CASE.OBJID = SA.TABLE_ACT_ENTRY.ACT_ENTRY2CASE AND
ID_NUMBER = ''' + @ID_NUMBER+ ''')'

PRINT @TSQL

Next we'll put it into EXEC(@TSQL) and an openquery.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tjl0079
Starting Member

10 Posts

Posted - 2009-02-25 : 19:10:02

ok i done that piece , how would i do the open query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources