Stored Procedures and nested SQL

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

SQL Server Development (2000)

Stored Procedures and nested SQL

Author

Topic

jkhome2
Starting Member

5 Posts

Posted - 2005-11-09 : 07:05:45

I'm converting some sql statements into stored procedures and i need some help. Say i have a query something like

'select field1,(select field2 from table2 where field3='something') as field4 from table1 where field5='somethingelse'

How would i go about putting it into a stored procedure. The reason i have the nested statement is becasue i would like the returned value to be part of a recordset.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-09 : 07:11:39

>>How would i go about putting it into a stored procedure.
Have you looked at Sql Server's Books Online: CREATE PROCEDURE?

create procecure mySP as
<Sql Statements>
go

>>The reason i have the nested statement is becasue i would like the returned value to be part of a recordset.

You could probably JOIN the 2 tables instead. Your method will work only if the nested statement returns 1 value.

Be One with the Optimizer
TG

jkhome2
Starting Member

5 Posts

Posted - 2005-11-09 : 07:16:16

Sorry what i should have said is that the nested statement is stored as a variable in the sp. I also have lots of select queries on the page each using this nested statement. The where part of the nested statement changes at each select statement.

I tried using EXEC but got errors

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 08:11:32

Do you want CrossTab?
Refer this
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Otherwise post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-09 : 08:33:17

>>I tried using EXEC but got errors
It's hard to help without the error message(s) and the EXEC statement that caused them

EDIT:
If I had to guess it's because you aren't doubling up the single quotes within your quoted string:

'select field1,(select field2 from table2 where field3=''something'') as field4 from table1 where field5=''somethingelse'''

Be One with the Optimizer
TG

jkhome2
Starting Member

5 Posts

Posted - 2005-11-09 : 08:45:35

Sorry this is my SP

DECLARE @foreignkey int
DECLARE @sqlstatement varchar(500)

SET foreignKey = 'SELECT F.COLUMN_NAME AS FKColumn_Name FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE F ON R.CONSTRAINT_NAME=F.CONSTRAINT_NAME AND R.CONSTRAINT_SCHEMA=F.CONSTRAINT_SCHEMA INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE P ON R.UNIQUE_CONSTRAINT_NAME=P.CONSTRAINT_NAME AND R.UNIQUE_CONSTRAINT_SCHEMA=P.CONSTRAINT_SCHEMA AND F.ORDINAL_POSITION=P.ORDINAL_POSITION WHERE P.table_name='

SET @sqlstatement = @foreignkey + '''tblwDocumentType'''
SELECT
DocumentTypeId,
Description,
(EXEC @sqlstatement) as foreignkey
FROM
tblwDocumentType

This gives an error message of incorrect syntax near EXEC but i have no idea why

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 08:56:49

What are you trying to get from that?

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-09 : 09:05:43

>>DECLARE @foreignkey int
>>SET foreignKey = 'SELECT...'

1. you are trying to set a variable of type INT to a string.
2. you didn't inlcude the "@" in front of "foreignKey"
3. you can't nest an EXEC statement as a column value.(see below)


--this is ok
exec('select ''tg'' as name')

--can't do this
select 'tg' as name1, (exec('select ''tg'' as name') as name2

--this is ok
select 'tg' as name1, (select 'tg' as name) as name2

Be One with the Optimizer
TG

jkhome2
Starting Member

5 Posts

Posted - 2005-11-09 : 09:05:50

the foreignkey field will store the the foreign key field name, basically i just want to know how to run that statement in that format as a nested query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources