Store procedure pass constant values

Posted by Murugavelmsc under Sql Server on 11/27/2013 | Points: 10 | Views : 1820 | Status : [Member] | Replies : 8

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi Experts,

I have write a sp which returns table of records (exec spname @param1).
I have to store the records in temptables with some constants values
eg

create table #temp(studif int, studname varchar(200), greetings varchar(200))

insert into #temp(studid, studname)
exec spname @param1 (after execute the sp it returns the records of column studid, studname)

But i want to pass constant value in the procedure and insert into the #temp table (greetings column)

insert into #temp(studid, studname, greetings)
exec spname @param1, 'Hello'

Please how can i achieve this?

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 11/27/2013 [Member] [MVP] Platinum | Points: 25

0	Fix: Define constant value at the time of temp table creation as follows: create table #temp(studif int, studname varchar(200), greetings varchar(200) default 'Hello') insert into #temp(studif, studname) exec spname @param1 (after execute the sp it returns the records of column studid, studname) Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Murugavelmsc, if this helps please login to Mark As Answer. \| Alert Moderator

Fix: Define constant value at the time of temp table creation as follows:

create table #temp(studif int, studname varchar(200), greetings varchar(200) default 'Hello')

insert into #temp(studif, studname)
exec spname @param1 (after execute the sp it returns the records of column studid, studname)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 11/27/2013 [Member] [MVP] Platinum | Points: 25

0	Please refer below link_ http://www.sqlteam.com/article/stored-procedures-parameters-inserts-and-updates Murugavelmsc, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: vishalneeraj-24503 on: 11/27/2013 [Member] [MVP] Platinum | Points: 25

0	Also refer: http://www.wiseowl.co.uk/blog/s263/parameters.htm Murugavelmsc, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Murugavelmsc on: 11/27/2013 [Member] Starter | Points: 25

0	Hi Bandi, Thanks for the response. I want to update the greeting columns dynamically exec spname @param1, 'Hello' exec spname @param1, 'Congrats' etc.... Regards, Murugavel S murugavel.sadagopan@gmail.com http://murugavelmsc.blogspot.in/ Murugavelmsc, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 11/27/2013 [Member] [MVP] Platinum | Points: 25

0	sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO SELECT , 'ConstantValue' FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', EXEC spname ') reference: http://blog.sqlauthority.com/2013/05/27/sql-server-how-to-insert-data-from-stored-procedure-to-table-2-different-methods/ Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Murugavelmsc, if this helps please login to Mark As Answer. \| Alert Moderator

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT *, 'ConstantValue'  FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
EXEC spname ')

reference:
http://blog.sqlauthority.com/2013/05/27/sql-server-how-to-insert-data-from-stored-procedure-to-table-2-different-methods/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/27/2013 [Member] [MVP] Platinum | Points: 25

0	--Alternate: create table #temp(studif int, studname varchar(200)) insert into #temp(studid, studname) exec spname @param1 SELECT , 'DynamicValues' As Greetings INTO #temp2 FROM #temp Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Murugavelmsc, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Allemahesh on: 11/27/2013 [Member] [MVP] Silver | Points: 25

0	You can see the complete code as below:- CREATE TABLE #STD(ID INT, NAME VARCHAR(500)) create table #TEMPSTD(ID INT, NAME varchar(500), GREETING VARCHAR(MAX)) INSERT INTO #STD EXEC spname @param1 INSERT INTO #TEMPSTD SELECT ID, NAME, 'DY' AS GREETING from #STD SELECT * FROM #TEMPSTD DROP TABLE #STD DROP TABLE #TEMPSTD Happy Coding, If it helps you or directs U towards the solution, MARK IT AS ANSWER Murugavelmsc, if this helps please login to Mark As Answer. \| Alert Moderator

You can see the complete code as below:-

CREATE TABLE #STD(ID INT, NAME VARCHAR(500)) 
create table #TEMPSTD(ID INT, NAME varchar(500), GREETING VARCHAR(MAX)) 

INSERT INTO #STD
EXEC spname @param1

INSERT INTO #TEMPSTD
SELECT ID, NAME, 'DY' AS GREETING from #STD

SELECT * FROM #TEMPSTD

DROP TABLE  #STD
DROP TABLE  #TEMPSTD

Happy Coding,
If it helps you or directs U towards the solution, MARK IT AS ANSWER

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/30/2013 [Member] [MVP] Platinum | Points: 25

0	Click on "Mark as Answer " Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Murugavelmsc, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts