SQL String question

kilika · November 26th, 2003, 07:57 PM

How do I execute some SQL code in a PROCEDURE when I have it in a string format

Code:

Create Proc test AS
 EXECUTE 'SELECT * FROM sometable'
GO

I know my code could be done without the string, but I have need of it to be in string format. Please if anyone knows how to do what the above says, I'd appreciate the help. The EXECUTE above does not work. Thanks for the help in advance,

Chris

jemacc · November 27th, 2003, 02:59 AM

I am not an expert at this, but from wht I see your trying to execute a proc which has not been created.

Create Proc test AS
EXECUTE ('SELECT * FROM sometable')
GO
then execute the procedure
exec test
If I am not clear please correct me

jemacc · November 27th, 2003, 03:01 AM

you may also executed directly

EXEC ('USE yourTable') SELECT * FROM sometable

Jeff Mason · November 27th, 2003, 07:58 AM

Quote:

quote:Originally posted by jemacc
you may also executed directly

EXEC ('USE yourTable') SELECT * FROM sometable

No.

The 'USE' statement is to used change the current database and has nothing to do with tables.

Note also that the scope of the USE statement in the EXECUTE above is only within the EXECUTE, and the current database will revert to the user's default database when the EXECUTE statement completes and the SELECT begins.

You say you "have need of it to be [a] string". Be aware that this quite inefficient and is a potential security risk, so be careful...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

kilika · December 1st, 2003, 11:25 AM

I appreciate all the help. This is really for a temp short term thing. I have an existing software package that uses SQL and I need to integrate with it. The problem is that this program package has nearly 1000 tables it uses. In the software I have put in some test data and I am trying to write something that will traverse throughout all the tables and find the data. I have some SQL that pulls out all the table names. I was just going to write something that traversed through the list of names and searched the tables for some certain data so I could find which tables I need to update. If anyone has any better ideas, I'd certainly be willing to entertain the ideas.

Thanks,

Chris

kilika · December 1st, 2003, 02:02 PM

I have the following SQL code:

Code:

Declare MyCursor CURSOR
GLOBAL
SCROLL
KEYSET
FOR
SELECT SO.[name] AS TABLENAME, SC.[name] AS COLUMNNAME
FROM sysobjects SO
LEFT JOIN syscolumns SC ON SO.id=SC.id
WHERE SO.xtype = 'U' 
ORDER BY SO.[name], SC.[name]

DECLARE @TableName nvarchar(100)
DECLARE @ColumnName nvarchar(100)
DECLARE @SQLString nvarchar(1000)
DECLARE @INITCount int

OPEN MyCursor

Fetch next from MyCursor INTO @TableName, @ColumnName

while @@fetch_status=0
Begin
    Set @SQLString = 'SELECT @INITCount = Count(*) FROM ' + @TableName + ' WHERE ' +  @ColumnName + ' LIKE ''%Tester%'''
    EXECUTE @SQLString
    If @INITCount > 0
        BEGIN
            Print @SQLString
        END
    Fetch next from MyCursor INTO @TableName, @ColumnName
End

close MyCursor
deallocate MyCursor

When I run it, I get an error:

Code:

Server: Msg 2812, Level 16, State 62, Line 24
Could not find stored procedure 'SELECT @INITCount = Count(*) FROM ABPECNT WHERE FLDCLASS LIKE '%Tester%''.

I do not know why it is trying to execute it as a stored proc. Anyone got any ideas how to make it execute the SQL string correctly? If I just print the line instead of execute it, I get a line of code that should execute correctly/. An example would be:

Code:

SELECT @INITCount = Count(*) FROM ABPECNT WHERE FLDCLASS LIKE '%Tester%'

Thanks in advance,

Chris

Jonax · December 1st, 2003, 02:07 PM

Change the line

Code:

EXECUTE @SQLString

to

Code:

EXECUTE sp_executesql @SQLString

kilika · December 1st, 2003, 02:15 PM

OK, I made the change and that fixed the problem. Now I am getting the error:

Code:

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@INITCount'.

I think it has something to do with the fact that I am trying to use one of my variables in the SQL listing the name as part of the string.

How would I execute the SQL and get a count of records returned as an INT back into some variable to test?

Chris

kilika · December 1st, 2003, 06:03 PM

Anyone have any ideas here?

Chris

melvik · December 2nd, 2003, 12:58 AM

Code:

Set @SQLString = 'SELECT '+@INITCount+' = Count(*) FROM ' + @TableName + ' WHERE ' +  @ColumnName

...

HTH.

Always:),
Hovik Melkomian.