Store procedure pass constant values

Posted by Murugavelmsc under Sql Server on 11/27/2013 | Points: 10 | Views : 1191 | Status : [Member] | Replies : 8
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/



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
--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

Up
0
Down
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

Up
0
Down
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

Login to post response