Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 26th, 2003, 07:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL String question

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
__________________
Chris
  #2 (permalink)  
Old November 27th, 2003, 02:59 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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

  #3 (permalink)  
Old November 27th, 2003, 03:01 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

you may also executed directly

EXEC ('USE yourTable') SELECT * FROM sometable

  #4 (permalink)  
Old November 27th, 2003, 07:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #5 (permalink)  
Old December 1st, 2003, 11:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #6 (permalink)  
Old December 1st, 2003, 02:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #7 (permalink)  
Old December 1st, 2003, 02:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 184
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Jonax
Default

Change the line

Code:
EXECUTE @SQLString
to

Code:
EXECUTE sp_executesql @SQLString

  #8 (permalink)  
Old December 1st, 2003, 02:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #9 (permalink)  
Old December 1st, 2003, 06:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Anyone have any ideas here?

Chris
  #10 (permalink)  
Old December 2nd, 2003, 12:58 AM
Friend of Wrox
Points: 3,558, Level: 25
Points: 3,558, Level: 25 Points: 3,558, Level: 25 Points: 3,558, Level: 25
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: California, USA
Posts: 996
Thanks: 2
Thanked 11 Times in 11 Posts
Send a message via Yahoo to melvik
Default

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

HTH.

Always:),
Hovik Melkomian.




Similar Threads
Thread Thread Starter Forum Replies Last Post
String Manipulation Question JMOdom C# 0 April 10th, 2007 11:38 AM
connection string question -Dman100- SQL Server ASP 7 August 17th, 2004 04:10 PM
Number String Question CNewbie C++ Programming 28 April 1st, 2004 07:59 PM
String in mc++ question Ereinion Visual C++ 0 November 7th, 2003 11:44 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.