SQL 2012 :: Get Structure Of Stored Procedure Output Table

Apr 15, 2014

To get the results of a stored proc into a table you always had to know the structure of the output and create the table upfront.

CREATE TABLE #Tmp (
Key INT NOT NULL,
Data Varchar );

INSERT INTO #Tmp
EXEC dbo.MyProc

Has SQL 2012 (or SQL 2014) got any features / new tricks to let me discover the table structure of a stored procedure output, - i.e treat it as a table

EXEC dbo.MyProc
INTO #NewTmp
or
SELECT *
INTO #NewTmp
FROM ( EXEC dbo.MyProc )

View 9 Replies


ADVERTISEMENT

Modify Table Structure Via Stored Procedure(?)

Jun 9, 2008

Our ERP vendor is helping us migrate from one version of their product to another. In the new version, they have added some columns and changed a few data types in several existing tables.

They sent us a list of the modifications that were necessary, which we did (painstakingly) by hand. But, for other reasons the update failed and we have to start anew.

This got me thinking: is there any way to do this kind of update via a script/stored procedure? I don't have their exact changes handy, but it was stuff like the table "inmast" needs a column of varchar(50) called "lotc" added to it, and the "intime" table needs the datatype of columns "Mon" "Tue" "Wed" changed from text to varchar(50), stuff like that.

Is it possible to do stuff like that in a SP?

View 1 Replies View Related

Pass Hash (#) Table With Different Structure To Stored Procedure

Jul 23, 2005

Dear Techies,I making one stored procedure, which does some operation based on aninterface hash (#) table ---- name #mydata.This stored has two section of code (seperated by parameter value 0and 1)But hash table #mydata (same name) number/name of columns changes asper call 0 or 1.e.g.when call for 0, ----> Pass 2 columns as company_cd and section_cd ininterface hash (#) table ---- name #mydata.when call for 1, ----> Pass 3 columns as Section_cd, line_cd andsubline_cd in interface hash (#) table ---- name #mydata.As a result, none of the case (0 or 1) is running properly, It givesproblem.When I execute procedure for 0 by passing #mydata with two columns---> it gives problem in 1 section codeAnd When I execute procedure for 1 by passing #mydata with threecolumns ---> it gives problem in 0 section codePlease suggest !!! If anybody have faced the same problem or have anyidea about this case.(I think passing hash table with 3 column as col1,col2,col3 can servethe purpose, but this may cause rework in my case, so looking foralternate solution)Thanks in Advance,T.S.Negi

View 1 Replies View Related

SQL Server 2012 :: Formatting XML Output - Avoid Normalizing Structure On Client

May 28, 2015

I have a script that resolve's data into xml like this, ex:

<root>
<title>A</title>
<id>1</id>
<nodes>
<node>
<id>2</id>
<title>A.1</title>
</node>
</nodes>
</root>

And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.

For the root I am using

FOR XML PATH('root'),TYPE; and for the hierarchy that follows
FOR XML RAW ('node'), root('nodes'), ELEMENTS

View 0 Replies View Related

Is It Possible To Use Table Output Parameter Of Stored Procedure? If Possible, How?

Jun 28, 2007

is it possible to use table output parameter of stored procedure? if possible, how?
 
thanks

View 2 Replies View Related

System Stored Procedure Output Into Table

Nov 29, 1999

Hi,
Is it possible to store the output of a SQL Server 7.0/6.5 System Stored Procedure (eg. xp_fixeddrives, sp_spaceused, etc.) in a table, possibly with a Select Into?
All help will be greatly appreciated.
Thanx in advance.
Craig

View 4 Replies View Related

Redirect Output Of A Stored Procedure To A Table

Feb 25, 2004

How to redirect output of a stored procedure to a table

View 3 Replies View Related

How To Store The Output Of Stored Procedure To A Temp Table

Jan 28, 2008

Hi all,

I've a requirement to store the output of the stored procedure into temp. tables/ table varibles.
I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.

Simplified SP is as...


Create procedure usp_test
as
begin

select c1,c2 from table1
select c3,4 from table2
select c9,c8 from table3
select c5,c7 from Table4
end

I'm expecting something like this...

declare @table1 table (c1, c2)
insert into @table1
Exec <Sp_Name>

select * from @table1

I know the above stmnt works, if my SP has only 1 select stmnt as output.
Please help me to acheive this for multiple select statements.

Thanks,

View 5 Replies View Related

T-SQL (SS2K8) :: How To Display Stored Procedure Output In Html Table Format

Mar 16, 2014

i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.

View 2 Replies View Related

SQL Server 2014 :: Insert Stored Procedure Output To Table And Add Datetimestamp

Jun 22, 2015

I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.

insert into Table_Test execute sp_who

View 2 Replies View Related

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

I am working on an OLAP modeled database.

I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).

I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.

The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.

What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?

I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.

Thanks in advance for any assistance you can provide.

View 9 Replies View Related

SQL Server 2012 :: Table Variable In Stored Procedure?

Jul 29, 2015

What is the purpose of creating table variable and inserting data into it and selecting from table variable inside of stored proc?

Why can't the stored proc just have select statement instead of creating table variable?

View 3 Replies View Related

Create Stored Procedure In IF-structure

Aug 14, 2007

Hi everyone,

I'm currently struggeling in creating some SQL script to create stored procedures. I found the following example on MSDN:




Code SnippetUSE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO






The thing is, I want to change and use it like this:

Code SnippetUSE pubsIF NOT EXISTS (SELECT name FROM sysobjects
CREATE PROCEDURE ...USE pubsGOALTER PROCEDURE au_info2 ...
But that does not seem to work.. I get the following error:
Code SnippetIncorrect syntax near the keyword 'PROCEDURE'
Any idea's? Any help is appreciated!
Kind regards,
Frederik

View 3 Replies View Related

SQL Server 2012 :: Stored Procedure - How To Return A Single Table

Nov 21, 2013

I have this SP

ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)
AS
DECLARE @steps bigint
SET @steps = DATEDIFF(hour, @start, @stop)/ @step
DECLARE @i bigint
SET @i=0

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Simple XML To Table Resultset From Stored Procedure

Oct 29, 2014

I got some xml that is essentially an html table that I need to turn into a standard table resultset from a stored proc. If you take this xml and save it as html that is the desired resultset I am looking for. I realize the <td> tags repeat so I would just prefer 'col' + positional index for the col name. Keep in mind that <td> could be is 1 to n.

<table>
<tr>
<td>cell1</td>
<td>cell2</td>
<td>cell3</td>

[Code] .....

This is my attempt but I can't figure out how to get separate cols

declare @GridData xml = '<table><tr><td>cell1</td><td>cell2</td><td>cell3</td></tr><tr><td>cell4</td><td>cell5</td><td>cell6</td></tr><tr><td>cell7</td><td>cell8</td><td>cell8</td></tr></table>'
select T.C.value('.', 'nvarchar(max)')
from @GridData.nodes('//tr') T(C)

View 6 Replies View Related

SQL Server 2012 :: Stored Procedure - How To Join Another Table Into Select Statement

Jan 7, 2014

I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).

The structure of the periods table is as follows:

[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL

The stored procedure is currently:

USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[Code] ....

What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.

For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23

So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.

View 1 Replies View Related

SQL Server 2012 :: How To Insert One To Many Valued Parameters From A Stored Procedure Into A Table

Jan 14, 2015

I have a SP with Parameters as:

R_ID
P1
P2
P3
P4
P5

I need to insert into a table as below:

R_ID P1
R_ID P2
R_ID P3
R_ID P4
R_ID P5

How can I get this?

View 2 Replies View Related

SQL Server 2012 :: Avoiding Temp Table With String Of IDs Passed Into Stored Procedure

Sep 26, 2014

Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.

Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] .....

The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] ....

The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?

View 9 Replies View Related

Putting Table Structure Into Word Using Procedure

Jan 22, 2006

Hi there

I have nearly 1000 table. I need all the table structure in word document. Is it possible to take table structure using Stored Procedure. If it is How to do that.

Thanx in advance

by
yasi

View 8 Replies View Related

SQL 2012 :: Store Procedure Only Output One Select Statement

May 28, 2014

There are about 10 select statements in a store procedure.

All select statements are need.

Is it possible to output only the result of last select statement?

View 2 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

Output Stored Procedure

Oct 18, 2006

 1 public static List<string> viewtree(int root)
2 {
3 SqlConnection con = new SqlConnection(mainConnectionString);
4 con.Open();
5 try
6 {
7 List<string> ids = new List<string>();
8 SqlCommand command = new SqlCommand(@"ShowHierarchy2", con);
9 command.Parameters.AddWithValue("@root", root);
10 command.Parameters.Add(new SqlParameter("@outstring", SqlDbType.VarChar));
11 command.Parameters["@outstring"].Direction = ParameterDirection.Output;
12 command.CommandType = CommandType.StoredProcedure;
13 //command.ExecuteScalar();
14 //ids = command.Parameters["@outstring"].Value.ToString();
15
16 SqlDataReader dr = command.ExecuteReader();
17 while (dr.Read())
18 {
19 ids.Add((dr["@outstring"].ToString()));
20 }
21 //command.Parameters.Clear();
22
23 return ids;
24 }
25 finally
26 {
27 con.Close();
28 }
29 }
 Can someone tell me why i'm getting the following error:String[1]: the Size property has an invalid size of 0. Thanks in advance

View 7 Replies View Related

How To Get Stored Procedure Output ?

Apr 3, 2004

I have a variable @NetPay as type money, and a stored proc spGetNetPay.
The output of spGetNetPay has one column NetPay, also with type of money, and always has one row.

Now I need assgin output from spGetNetPay to user variable @NetPay. How can I do That?

Set @NetPay = (Exec spGetNetPay) Sorry this does not work. Is it possible to create a user defined function?

I have little knowledge about User defided function. Is is the way I should go?

Thanks.

David J.

View 3 Replies View Related

How To Use OUTPUT In Stored Procedure

Jul 4, 2001

I'm calling a stored procedure from outside program using
Execute SPid(a stored procedure).

In the SPid procedure i want to return a value using
Output.

Can any one tell me how do it since i never used it before ?

View 3 Replies View Related

Stored Procedure Output

Sep 27, 2000

Hi
I am running a stored procedure which first puts the data in a temp table and then gives the output...
the output is supposed to generate a report based on data from temp table

However when i run it, the first 2 statements are

(15345 row(s) affected)


(407 row(s) affected)

abd then the select statement runs...due to this, the report in ASP returns an error...does anyone know how i can suppress the first 2 lines and get only the actual data as output

any help will be appreciated

regards,

View 3 Replies View Related

Immediate Output From Stored Procedure.

Apr 11, 2002

When I run a script in query analyzer using a script (A "GO" statement exists after each SQL) I get the results on screen as soon as each query completes. When I run thru stored proc, I can get the result only after the whole procedure completes execution. Is there any way to get the outpout immediately as soon as each query completes? This will be useful in tracking thre progress of a stored proc.
Thanks
Satish

View 2 Replies View Related

Stored Procedure Output

Feb 8, 2007

I am using a stored procedure to query tables and format a record to write to a file. Below is the syntax I'm using to do the write.

set @cmd = 'echo ' + rtrim(@patient_rec) + ' >> f:output
ecall.csv'
exec master..xp_cmdshell @cmd

The procedure will write about 30,000 records or so and then quit writing to the file. Is there a setting I have to modify to write more records or is there a better way to do this?

View 6 Replies View Related

Output Stored Procedure

May 25, 2006

Dear All,How can I show the resultrecords of a SP.I can be done by doubleclick the SPname?But how to do it by code.I want the following interfaceIn my form the user1 selects a SP (combobox showing a userfrinly name)2 adds the related parameters3 and then click the show result-buttonbut the .execute command doen't show teh records.I want the same output as you have doublclicking the SPname in theobjectwindow.Thanks,Filip

View 1 Replies View Related

Stored Procedure Output Parameter

Aug 19, 2007

  I have two stored procedures one generates an output parameter that I then use in the second stored procedure.
  1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4
5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14
15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut"
17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20
21
22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " "
30 myCommand.Parameters("@IDFieldOut").Value = 031
32 myCommand.ExecuteNonQuery()33
34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35
  If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas

View 6 Replies View Related

No Output From The Stored Procedure In The Dataset

Oct 23, 2007

HiI have this code snippet[CODE]   string connstring = "server=(local);uid=xxx;pwd=xxx;database=test;";            SqlConnection connection = new SqlConnection(connstring);            //SqlCommand cmd = new SqlCommand("getInfo", connection);            SqlDataAdapter a = new SqlDataAdapter("getInfo", connection);            a.SelectCommand.CommandType = CommandType.StoredProcedure;            a.SelectCommand.Parameters.Add("@Count", SqlDbType.Int).Value = id_param;            DataSet s = new DataSet();                        a.Fill(s);            foreach (DataRow dr in s.Tables[0].Rows)            {                Console.WriteLine(dr[0].ToString());            }[/CODE] When I seperately run the  stored procedure getInfo with 2 as parameter, I get the outputBut when I run thsi program, it runs successfully but gives no output Can someone please help me? 

View 1 Replies View Related

Stored Procedure && Output Parameter

Feb 3, 2008

I have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record.  The procedure works fine, but I'm unable to get that return value in my code.  Here's what I have:
 IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL
DROP PROCEDURE dbEvent
GO
CREATE PROCEDURE
@Name varchar(200)
,@Location varchar(200)
AS
INSERT INTO Event
(
NAME
, LOCATION
)
VALUES
(
NAME
, @LOCATION
)
SELECT SCOPE_IDENTITY() 
 
And my code behind: public Int64 Insert(SqlConnection Conn)
{
try
{
using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output;
Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName;
Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location;
if (Conn.State != ConnectionState.Open) Conn.Open();
Command.ExecuteNonQuery();
Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString());
return _requestId;
}
 I'm getting the error that I have "Too many arguments specified" in my Insert() method.  When I test the procedure in Query Analyzer, it works fine and returns the correct value.
Any suggestions?  Don't I need to declare that return value in my .NET code as an output parameter?

View 2 Replies View Related

Stored Procedure With Output Parameters

Feb 11, 2008

i built a stored procedure with inserting in to customers table.
i have one column with identity.
so want to take that identity column value in the same stored procedure.
so how can i write that procedure with insert in to statements in that stored procedures.

can any one tell me.
also how to get that value in ado.net 2.0.
friends please tell me.

View 3 Replies View Related

Stored Procedure Output Parameters

Aug 3, 2004

Hi

I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.

I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters:
SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt);
param.Direction=ParameterDirection.Output;
even if I only need the value of a single one.
Is this right? Is there a way to avoid coding every one every time?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved