Solved

execute a string

Posted on 2009-07-08
8
329 Views
Last Modified: 2012-05-07
I am trying to create a string dynamically and then get it executed.  My problem is that the string may be 100 chars long or up to 600 chars.  I can get the sql to create the srting in the correct syntax.  I have used a very simple of select in order to make it easy

my problem is that the code below Parses Ok but when I execute it says

Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 's'.

This is confusing as I am not trying to create a SP

Also, is there specific syntax that I am missing

Any ideas please

Thanks from the novice

Adrian
declare @string as varchar
 
set @string = 'select * from [dbo].[expenseheader]'
 
EXEC @string

Open in new window

0
Comment
Question by:wrcplc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 110 total points
ID: 24801875
Hope this helps:
declare @string nvarchar(1000);
set @string = 'select * from [dbo].[expenseheader]';
EXEC sp_executesql @string;

Open in new window

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 70 total points
ID: 24801878
2 errors: varchar must be size specified
exec (string), oitherwise string is considered a stored proc
declare @string as varchar(200)
 
set @string = 'select * from [dbo].[expenseheader]'
 
EXEC(@string)

Open in new window

0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 110 total points
ID: 24801883
>> EXEC @string

As you call exec statement here it searches for a procedure next and throws out that error.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 7

Assisted Solution

by:Chandan_Gowda
Chandan_Gowda earned 70 total points
ID: 24801890
declare @string as varchar
set @string = 'select * from [dbo].[expenseheader]'
EXEC (@string )
0
 
LVL 7

Assisted Solution

by:Chandan_Gowda
Chandan_Gowda earned 70 total points
ID: 24801897
you dont have to change anything...Just add brackets
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24801910
angelIII,

I suggested using sp_executesql since this syntax EXEC(@string) will be deprecated after 2005
And string should be declared as nvarchar instead of varchar.

Kindly correct if I am wrong.
0
 

Author Closing Comment

by:wrcplc
ID: 31601011
Thank you all.  So many so quick.

Sorry to have taken your time but feel safe in the knowledge you have all saved me from hours of research

Again, thanks

adrian
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24801944
Hi,
>I suggested using sp_executesql since this syntax EXEC(@string) will be deprecated after 2005
new to me, but good to know.
you could have put that in the first comment :)

>And string should be declared as nvarchar instead of varchar.
for sp_executesql: yes, required.
for exec: not required

for  the rest, I explained the 2 original issues with the code in my comment.

a3
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Restrict result set 1 49
LAG_ROWID - how do I get the right order using this query? 2 28
SQL query 7 50
T-SQL: How to append a column for serialized JSON data? 2 51
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question