Inserting result value into a table.

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)

Inserting result value into a table.

Author

Topic

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-21 : 14:32:27

Hey guys I'm trying figure out if there is a way to insert a result from a stored procedure into a table without going through ASP.NET. This is just an experiment and for learning purposes since i did manage to work it in .NET.

For example.

CREATE PROCEDURE dbo.inserttoLog

@Modem_ID VARCHAR(7)

As

Select dbo.PRODUCT.MODEM_ID + ' ' + dbo.PRODUCT.COLOUR + dbo.PRODUCT.SPEED AS Part#
FROM PRODUCT
WHERE ([Part#] LIKE '%' + @Modem_ID + '%')

I want to take the concatenated result of PART# and insert it into a log table. Is there a way of doing that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 15:06:38

CREATE TABLE SomeTable(...)

INSERT INTO SomeTable (...)
EXEC dbo.inserttoLog @Modem_ID = ...

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-21 : 15:21:18

thanks tkizer, but i was hoping to grab the whole concatinated value. The reason i would want to do it this way is to control what gets put into the search log. If i went by textBox.text it would put whatever variation the customer tried. But if i insert the result set it would be controlled and clear. Do you know what i mean?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 15:24:24

That's what my solution would do. It will grab whatever is outputted from your stored procedure and put it into a table. Did you even try what I posted?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-21 : 16:25:01

i did try it but i must be confused (which is nothing new) LOL. It probably stems from the original stored procedure being labeled incorrectly. Let's say

CREATE PROCEDURE dbo.getProductName

@Modem_ID VARCHAR(7)

As

Select dbo.PRODUCT.MODEM_ID + ' ' + dbo.PRODUCT.COLOUR + dbo.PRODUCT.SPEED AS Part#
FROM PRODUCT
WHERE ([Part#] LIKE '%' + @Modem_ID + '%')

I was just adding your code to the bottom of this code, are you saying I should create another stored procedure that looks like this?

Create procedure dbo.insertLog

INSERT INTO dbo.logtable(SearchString)
EXEC dbo.inserttoLog @Modem_ID =

?

I'm sorry to sound so stupid and new, do i put something in after the = sign?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 16:31:18

You do this outside the stored procedure.

CREATE TABLE #temp (PartNo varchar(50))

INSERT INTO #temp (PartNo)
EXEC dbo.getProductName @Modem_ID = PutYourInputValueHere

SELECT * FROM #temp

DROP TABLE #temp

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-21 : 17:22:48

i keep getting this error

Insert Error: Column name or number of supplied values does not match table definition.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 17:24:34

Have you shown us the actual stored procedure?

What does this return: EXEC dbo.getProductName @Modem_ID = PutYourInputValueHere

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-21 : 18:17:39

oh! it works!! But things just got way more complicated. I need this to execute along side anther stored proc that shoots back the search results.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 18:52:55

You'll have to be more specific, perhaps by showing us code or sample data.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-22 : 02:30:13

First, I really appreciate your help!!

Here is the code as I have it. Right now I have a BTN.CLICK statement in my ASP.NET code to execute this StoredProc to return a result set.

ALTER PROCEDURE DBO.GETPRODUCTID

@PartNo char (7),

as

Select PartNo+' '+Region as PartNo, CASE Black WHEN 'Y' THEN '(Black)' ELSE ''END+''+CASE White WHEN 'Y' THEN '(White)' ELSE '' END as Details,
CASE WHEN dbo.inventory.QOH >0 THEN 'YES' ELSE 'NO' END AS Availability
From dbo.Product INNER JOIN dbo.inventory ON ProductID=PartNo+' '+Region
WHERE ([PartNo] LIKE '%' + @PartNo + '%'

That code returns

Product Details AVAILABILITY
Mod338 CA WHITE BLACK NO

I want to automatically take MOD338 CA and have that inserted into a log table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-22 : 13:06:42

You need to create the table (#temp in my example) so that it matches the output of your stored procedure. Once the data is in that table, you can extract whatever you need from it.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-22 : 22:09:50

OKAY i've figured it out. My logic was backwards. I was imaging the query pulling the information from my inital script and then the second stored proc would take the result and put that into a log. What actually is happening is that the info is being stored in the log and then being pulled from that stored table!! DUH... thank you so much for your patience and especially your help, tkizer!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources