Error near this exec, what am I doing wrong? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Error near this exec, what am I doing wrong?

Author

Topic

mzwebbie
Starting Member

15 Posts

Posted - 2003-05-29 : 10:02:50

I've got the following code in a stored procedure. The rest of it works just fine, my SQL statment is what I expect. Now I'm trying to get the results of this select into the variable @2ndID so I can compare it to another. But, I keep getting the error 'incorrect syntax near EXEC' when I try to run it (well,alter the procedure actually). I'm fairly new at this, what am I doing wrong?? Help!!

set @cmdSelect = 'SELECT intOpt_ID From ' + @dbName + 'dbo.tblOptioneeTerms WHERE intOpt_ID = ''' + @optid + ''''

set @2ndID = EXEC (@cmdSelect)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-29 : 10:07:17

Does @dbName have a . at the end

mzwebbie
Starting Member

15 Posts

Posted - 2003-05-29 : 10:18:03

Oops.
I put in the dot before dbo. But I still keep getting the same error. This is driving me crazy!
I originally had it set up without assigning it to a variable. Like
SELECT @2ndID = intOpt_ID From tblOptioneeTerms WHERE intOpt_ID = @optid

but I realized I needed to put the whole stored procedure in another database, so needed to dynamically generate the select statement (that tbl is in another database which will change each time)

help...

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-29 : 10:20:30

If you run this in query analyzer while using another database what do you see for results

SELECT intOpt_ID From databaseyouwant.dbo.tblOptioneeTerms WHERE intOpt_ID = 'valueyouwant'

skond
Yak Posting Veteran

55 Posts

Posted - 2003-05-29 : 10:45:30

You can return value using EXEC. You can do one of the following things to solve your problem.

1.

set @cmdSelect = 'SELECT intOpt_ID From ' + @dbName + '.dbo.tblOptioneeTerms WHERE intOpt_ID = ''' + @optid + ''''

create table #temp
(
intopt_id int
)

insert into #temp
EXEC (@cmdSelect)

select intopt_id from #temp

2.
declare @nsql_str nvarchar(4000),@inopt_id int

set @nsql_str = 'SELECT @inopt_id = intOpt_ID From ' + @dbName + '.dbo.tblOptioneeTerms WHERE intOpt_ID = ''' + @optid + ''''

EXEC sp_ExecuteSQL @nsql_str, N'@inopt_id int out', @inopt_id OUT

mzwebbie
Starting Member

15 Posts

Posted - 2003-05-29 : 10:51:47

It works as expected and returns the id.

I also tried putting that output sql statement directly into the EXEC like
set @2ndID = Exec("the whole darn statment")
and I got that same error about the exec

mzwebbie
Starting Member

15 Posts

Posted - 2003-05-29 : 11:02:07

Can you explain a little more whats going on in the second one?

The first one might work, but will it slow things down a lot? I've got all this in the middle of a big old loop...here is the whole thing so you can get a better idea. If there is a better way, let me know. I need to dynamically pass in the name of the database from an ASP page. All that was working great, but then I realized I needed to move the proc. so I had to move it all into the web database.

Hope that made sense. Thanks for all your help.

---

BEGIN
SET @cmdCursor='DECLARE UserCursor Cursor FOR SELECT OPT_ID FROM ' + @dbName + '.dbo.optionee Where ENRL_IN_ESP=1 OR ENRL_IN_EO=1'
EXEC (@cmdCursor)
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @optid

WHILE @@FETCH_STATUS = 0 BEGIN
BEGIN
set @SecondID = ''
set @cmdSelect = 'SELECT intOpt_ID From ' + @dbName + '.dbo.tblOptioneeTerms WHERE intOpt_ID =''' + @optid + ''''
Print @cmdSelect

--This is the line it doesn't like
set @SecondID = EXEC(@cmdSelect)

IF @SecondID <> @optid
BEGIN
Select @cmdInsert = 'INSERT INTO ' + @dbName + '.dbo.tblOptioneeTerms (intOPT_ID, privacy, terms ) VALUES (''' + @optid + ''',0, 0)'
EXEC (@cmdInsert)
SELECT @tmpError = @@ERROR
IF @tmpError <>0 --If there are errors, write out errors to tblRefreshExceptionLog
BEGIN
IF @tmpError = 2627
SET @tmpErrorDesc = 'Error inserting duplicate record.'
ELSE
SELECT @tmpErrorDesc = description FROM master.dbo.sysmessages WHERE error = @tmpError

INSERT INTO [Web].[dbo].[tblRefreshExceptionLog]([OptioneeID], [DatabaseName], [ErrorNumber], [ErrorDescription], [DateOccured], [UserID])
VALUES(@optID, @dbName, @tmpError, @tmpErrorDesc, GetDate(), 'sa')
SET @counterrors = @counterrors + 1
END
ELSE --Else just count another insert
set @countinserts = @countinserts + 1
END
FETCH NEXT FROM UserCursor INTO @optid
END --end of while

END

skond
Yak Posting Veteran

55 Posts

Posted - 2003-05-29 : 11:18:04

If all you want to do is to enforce uniqueness, then why not create an unique index on that column.

you can also check for uniqueness like this:

select intOpt_ID from table group by intOpt_ID having count(*) > 1

But, as far as the second option in my previous email:

You can only return values when you use sp_ExecuteSQL and not EXEC.

I have just shown you how the syntax looks.

mzwebbie
Starting Member

15 Posts

Posted - 2003-05-29 : 11:27:50

Can I do something to see if:
set @cmdSelect = 'SELECT intOpt_ID From ' + @dbName + '.dbo.tblOptioneeTerms WHERE intOpt_ID =''' + @optid + ''''
Print @cmdSelect

found any records? Is there a property or something, like you can do .EOF or recordcount with a record set? I only want to insert if there are no records, so I could change it to say if the select didn't find any records, insert one, otherwise skip it.

skond
Yak Posting Veteran

55 Posts

Posted - 2003-05-29 : 11:47:54

Do this:

declare @nsql_str nvarchar(4000),@count int

set @nsql_str = 'SELECT @count = count(*) From ' + @dbName + '.dbo.tblOptioneeTerms WHERE intOpt_ID = ''' + @optid + ''''

EXEC sp_ExecuteSQL @nsql_str, N'@count int out', @count OUT

if @count > 0
print 'record exists'
else
print 'record does not exist'

mzwebbie
Starting Member

15 Posts

Posted - 2003-05-29 : 12:06:18

Oh...big..DUH!!

Sometimes you get so stuck going down one path, you forget about obvious things.

I think I got it working though like this:

BEGIN
set @SecondID = ''
set @cmdSelect = 'SELECT intOpt_ID From ' + @dbName + '.dbo.tblOptioneeTerms WHERE intOpt_ID =''' + @optid + ''''
Print @cmdSelect

--This is the line it doesn't like
EXEC(@cmdSelect)
set @tmpRowCount = @@ROWCOUNT
PRINT 'ROWCOUNT is' + CONVERT(char(10),@tmpRowCount)

IF @tmpRowCount = 0
BEGIN
PRINT 'going to insert ' + @optid
Select @cmdInsert = 'INSERT INTO ' + @dbName + '.dbo.tblOptioneeTerms (intOPT_ID, privacy, terms ) VALUES (''' + @optid + ''',0, 0)'
EXEC (@cmdInsert)

------------
But maybe just doing a count would be better. Sounds like it might be. Hmm...

Thank you soo much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources