sp problem - SQL Server Forums

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

Transact-SQL (2005)

sp problem

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2009-01-26 : 13:47:37

HI all,

I have a sp I need help with, I am doing an insert on a select - this part works fine:

INSERT Hits(CaseID, AcctNumber, FullName)
SELECT C.CaseID, C.AcctNumber, C.FullName
From Cases as C
Where C.Number = '123'

My problem is I also have some parameters I need to pass in to use in the insert as well -

Can anyone help with this or point me in the right direction of how to do this?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 13:59:26

Here's an example:

CREATE PROC SomeProc
(@var1 int, @var2 varchar(5), @var3 OUTPUT)
AS

INSERT Hits(CaseID, AcctNumber, FullName)
SELECT C.CaseID, C.AcctNumber, C.FullName
From Cases as C
Where C.Number = '123' AND SomeColumn = @var1 AND SomeOtherColumn = @var2

SET @var3 = @@ROWCOUNT

GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ann
Posting Yak Master

220 Posts

Posted - 2009-01-26 : 14:16:25

That's not going to work, what I need is something like this:

CREATE PROC SomeProc
(@var1 varchar(50))
AS

INSERT Hits(CaseID, AcctNumber, FullName, comment = @var1)
SELECT C.CaseID, C.AcctNumber, C.FullName
From Cases as C
Where C.Number = '123'

sorry, I probably didn't explain myself correctly

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 14:22:07

Yes you definitely didn't provide enough info to provide a good sample. Here you go:

CREATE PROC SomeProc
(@var1 varchar(50))
AS

INSERT Hits(CaseID, AcctNumber, FullName, comment)
SELECT C.CaseID, C.AcctNumber, C.FullName, @var1
From Cases as C
Where C.Number = '123'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ann
Posting Yak Master

220 Posts

Posted - 2009-01-26 : 14:34:29

That doesn't work - it doesn't update anything, I get a returned value = 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 14:37:23

It is very hard to assist with this as you aren't providing enough information. Show us exactly what you passed to the stored procedure and then also let us know if this returns the correct data:

SELECT C.CaseID, C.AcctNumber, C.FullName, 'ReplaceWithVar1Value'
From Cases as C
Where C.Number = '123'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ann
Posting Yak Master

220 Posts

Posted - 2009-01-26 : 14:51:08

Sorry - I messed up, when I was debugging I didn't refresh the sp ... your solution works fine .. sigh... been a long day already and it's only 11:00 am for me...

Thanks for your help, I really do appreciate it

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 14:51:30

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources