Want A Stored Procedure To Dynamically Select Views Pulled From A Table.
Feb 1, 2007
I am building a dashboard features that allows user to select reports from a dropdownlist. It is pulling from a table called Reports (cols: ReportID, Description, sqlView) In this Report table the report is associated to a view that queries the report.
And the user's selections are stored in table called UserReport (cols: userID, ReportID, createDt) .
I need to get a Dataset to contain datables of all reports selected. (for example a user select 3 reports, the dataset should contain 3 datables that represent the report).
I want to accomplish this by create a store procedure that queries the Reports table and then dynamically executes the views that related to the user selected reports. Can anyone give me an example on how to create the storeprocedure?
Thanks,
CG
View 3 Replies
ADVERTISEMENT
Mar 10, 2008
hi need help
how to "select from pivot stored procedure and insert into table"
this line
INSERT INTO [nili].[dbo].[tb_pivot_edit]
tnx
Code Snippet
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080401', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @NumDays = 28
BEGIN
SELECT p.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28])
) AS p
END
ELSE IF @Numdays = 30
BEGIN
SELECT p.ID,p.new_unit,p.mhlka_id,p.mhlka,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]
FROM (
SELECT ID,new_unit,mhlka_id,mhlka,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30])
) AS p
INSERT INTO [nili].[dbo].[tb_pivot_edit]
([Fname]
,[new_unit]
,[mhlka_id]
,[mhlka]
,[fld1]
,[fld2]
,[fld3]
,[fld4]
,[fld5]
,[fld6]
,[fld7]
,[fld8]
,[fld9]
,[fld10]
,[fld11]
,[fld12]
,[fld13]
,[fld14]
,[fld15]
,[fld16]
,[fld17]
,[fld18]
,[fld19]
,[fld20]
,[fld21]
,[fld22]
,[fld23]
,[fld24]
,[fld25]
,[fld26]
,[fld27]
,[fld28]
,[fld29]
,[fld30]
,[fld31])
END
View 1 Replies
View Related
Jun 8, 2006
Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?
I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:
CREATE TABLE < t > FROM SELECT id, name FROM < w > ...
Thank you!
View 6 Replies
View Related
Jul 23, 2005
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies
View Related
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
Oct 31, 2005
Hello,I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:insert into Table1( id, name)select id, namefrom Table2Can you do something like:exec spProc @id = id, @name = namefrom Table1Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.Thanks.
View 1 Replies
View Related
Dec 3, 2006
I want to dynamically determine which column I select data from. The table I want to select from looks like this:tblSexualitySexualityID int PKEN nvarchar(20)NL nvarchar(20)DE nvarchar(20)Based on the value of variable @LanguageColumnName I want to select the value from either column "EN", "NL" or "DE"In the sample below the value of @LanguageColumnName is "NL".Unfortunately the value of sexualityname is ALWAYS: "tblSexuality.NL" and not the value from that specific column...how can I alter my procedure so that it does the select the value from that column?ALTER PROCEDURE [dbo].[spFindUsers]@LanguageColumnName nvarchar(3),@startRowIndex int,@maximumRows intASBEGINSET NOCOUNT ON;
declare @tblSexualityName nvarchar(40)set @tblSexualityName='tblSexuality.' + @LanguageColumnName SELECT UserName,UserCode,ThumbNailPicture,BirthDate,ShowAgeOnly,IsMale,NearestBigCity,DistanceToNearestBigCity,Description,IsDonator,IsVIP,SexualityName FROM(SELECT ROW_NUMBER() OVER (ORDER BY UserCode) as RowNum,tblUserData.UserName,UserCode,IsDonator,IsVIP,Description,BirthDate,IsMale,ShowAgeOnly,tblCountries.CountryPicture,@tblSexualityName as sexualityname,tblCountries.CountryName,ThumbNailPicture,LastActivityDate,UserRanking,NearestBigCity,DistanceToNearestBigCity FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID INNER JOIN tblCountries ON tblUserData.Country=tblCountries.CountryID INNER JOIN tblSexuality ON tblUserData.Sexuality=tblSexuality.SexualityID) as MemberInfoWHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)END
View 1 Replies
View Related
Feb 28, 2005
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?
ALTER PROCEDURE dbo.spGetNextPageRecords
(
@pageSize int,
@previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN
View 4 Replies
View Related
Sep 22, 2014
I set up a stored procedure. My stored procedure has 8 variables declared at the beginning like this:
===========================
declare@IncurredDateStartsmalldatetime
,@IncurredDateEndsmalldatetime
,@PaidDateStart_CUsmalldatetime
,@PaidDateEnd_CUsmalldatetime
,@PaidDateStartsmalldatetime
,@PaidDateEndsmalldatetime
[Code] ....
All of these dates are based on the update of our data warehouse. This stored procedure runs a 5 step process and produces data for 8 - 10 monthly reports.
I was wondering if these variables can be updated dynamically and if they can how it is done.
View 1 Replies
View Related
Feb 28, 2007
I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?
EXAMPLE:
SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable
OBJECTIVE:
Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')????)
Thanks for any help
View 1 Replies
View Related
Feb 8, 2008
Hello All,
I have created SP in SQL 2K5 and make the where clause as parameter in the Sp. i am passing the where clause from my UI(ie ASP.NET), when i pass the where clause to SP i am not able to fetch the results as per the given criteria.
WhereClause from UI: whereClause="where DefectImpact='High'"
SQL Query in SP: SELECT @sql='select * from tablename'
Exec(@sql + @whereClause )
Here i am not able to get the results based on the search criteria. Instead i am getting all the results.
Please help me in this regard.
Thanks,
Subba Rao.
View 11 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
May 18, 2015
How Can I select Table Dynamically from in Side SQL Query
i.e.,
Select * from (Here I want Select the Dynamically from other Query)
View 6 Replies
View Related
Sep 24, 2006
Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €œcustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
Thanks
View 1 Replies
View Related
Aug 4, 2015
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
View 9 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Jul 23, 2005
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies
View Related
Apr 2, 2008
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance
ripern
-- Retrieves the value of column @columnName for credential id @credID
ALTER PROCEDURE [dbo].[PMC_User_GetUserValue]
@credID int,
@columnName nvarchar(50)
AS
DECLARE @userDataTable nvarchar(50)
DECLARE @userDataID int
DECLARE @statement nvarchar(500)
SET @statement = ' '
SET @userDataID =
(SELECT PMC_UserMapping.fk_userDataID
FROM PMC_UserMapping
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable =
(SELECT PMC_UserType.userDataTable
FROM PMC_UserType
INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName )
BEGIN
EXEC (@statement)
END
View 12 Replies
View Related
Mar 17, 2008
If I have a view such as: SELECT T.* FROM T
When I add a column to table T the view is not updated to reflect that change.
Furthermore, if there are other columns after the * in the view (for example SELECT T.*, GETDATE() as "My Date" FROM T) the last columns will contain incorrect data.
Is there a work around for this? An "auto-recompile when tables are modified" kind of option?
Thanks
Nick
PS: This is the script I used for testing:
create table tt (
test1 int primary key,
test2 int)
go
insert into tt (test1, test2) values (1,2)
go
create view vw_tt as select *, getdate() as "My Date" from tt
go
select * from vw_tt
go
create view vw_tt2 as select * from tt
go
alter table tt add test3 int
go
select * from vw_tt
select * from vw_tt2
select * from tt
drop table tt
drop view vw_tt
drop view vw_tt2
View 9 Replies
View Related
Jul 20, 2005
Hi all,We have an application through which we are bulk inserting rows into aview. The definition of the view is such that it selects columns froma table on a remote server. I have added the servers usingsp_addlinkedserver on both database servers.When I call the Commit API of oledb I get the following error:Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.I would like to know if we can bulk insert rows into a view thataccesses a table on the remote server using the "bulk insert" or bcpcommand. I tried a small test through SQL Query Analyser to use "bulkinsert" on a such a view.The test that I performed was the following:On database server 1 :create table iqbal (var1 int, var2 int)On database server 2 (remote server):create view iqbal as select var1,var2 from[DBServer1].[SomeDB].[dbo].[iqbal]set xact_abort onbulk insert iqbal from '\MachineIqbaliqbaldata.txt'The bulk insert operation failed with the following error message:[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData(CheckforData()).Server: Msg 11, Level 16, State 1, Line 0General network error. Check your network documentation.Connection BrokenThe file iqbaldata.txt contents were :112233If the table that the view references is on the same server then weare able to bulk insert successfully.Is there a way by which I should be able to bulk insert rows into aview that selects from a table on a remote server. If not then couldanyone suggest a workaround. I would actually like to know someworkaround to get the code working using OLEDB. Due to unavoidablereasons I cannot output the records to the file and then use bcp tobulk insert the records in the remote table. I need to have some wayof doing it using OLEDB.Thanks in advanceIqbal
View 7 Replies
View Related
Nov 24, 1999
Hi,
I need to regenerate a large view each month which performs a union across a number of partitioned tables. (DB is SQL Server 7)
The select statement for each table within the union is large and cannot be replaced with 'select *'.
One possible approach is to obtain the current view definition from syscomments and append an additonal 'UNION select ... from ...' to the end. However I'm having difficulties getting the full textual definition back into variables with the stored procedure. Getting the view definition from the information schema returns a truncated version.
Is there a way to get the whole of the description back (may be >8k)?
I have similar problems manipulating large text fields within the stored proc. If I try to build the entire view definition each period I hit the problem that EXEC cannot take a TEXT parameter (and a varchar isn't big enough).
There are some (messy) ways around this problem, but has anyone had a similar situation where large statements have have to built up before passing them to an EXEC statement and found a neat solution?
View 1 Replies
View Related
Apr 9, 2015
I am having one store procedure which use to load data from flat file to staging table dynamically.
Everything is working fine.staging_temp table have single column. All the data stored in that single column. below is the sample row.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
After the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.
If you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.
Output should be like below.
AB¯ALBERTA ¯93
AI¯ALBERTA INDIRECT ¯94
AL¯ALABAMA ¯30
How to achieve above scenario...
View 1 Replies
View Related
May 25, 2008
Hi, I want to set the max and min of a query to the variable @Value1 and @Value2. However, when I do the following, both @Value1 and @Value2 pointed to the latest data. Do I have to use a subquery to do that or is that possible to do it directly?
DECLARE @Value1 DECIMAL(7,2)
DECLARE @Value2 DECIMAL(7,2)
SELECT TOP 20 @Value1=MIN(Value1), @Value2=MAX(Value2)FROM OrdersGROUP BY OrderID, Date
View 9 Replies
View Related
May 4, 2006
I have the stored procedure snipet below and it does not return anything.
What am I doing wrong here? I works ok if I need all the records for the dataset and don't use the WHERE and LIKE.
CREATE PROCEDURE dbo.search
@Field VARCHAR (20), @KeyWord VARCHAR (200), @errCode INT OUTPUT
ASBEGIN
DECLARE @guid uniqueidentifier DECLARE @subject NVARCHAR SELECT @guid = guid, @subject = subjectFROM myTable WHERE @Field LIKE @KeyWord
SELECT guid, subjectFROM myTable WHERE @Field LIKE @KeyWord
Thanks,
Zath
View 9 Replies
View Related
Jan 12, 2001
In Informix you can use a stored procedure as part of a SELECT statement:
SELECT ord_num, ord_date, ship_date, business_day_diff(ord_date, ship_date)
FROM order_table
business_day_diff is a stored procedure that uses a custom calendar to compute
business days. In fact, Informix stored procedures can return any data type. Apparently SQL Server stored procedures cannot be used in a SELECT like this.
Does anyone know of a straightforward way to accomplish the same purpose?
View 2 Replies
View Related
Aug 12, 2007
Say I have the following stored procedure that takes in 1 parameter, and to call it I would do this: exec stored_procedure_name 'param'
And it returns a result with 3 fields: column1, column2, column3
Now I'm only interested in getting the results for column1, is there a way to call the stored procedure to only return column1?
I tried this: select column1 from (exec stored_procedure_name 'param')
It doesnt' work...
View 2 Replies
View Related
Dec 21, 2004
Hello!
How do you use the value of one select statement within another select statement inside the same stored proc?
Here is my scenario: I need a row id from one select statement to make another select statement. I figured instead of making two calls from within my code I could just call one stored procedure which would have to be faster!
I have written plenty of select statement sp's but this is the first time I ever needed to do something like this.
Any suggestions?
View 7 Replies
View Related
Oct 13, 2005
How could I use SELECT TOP in stored procedure
For ex I want convert this line to Stored procedure
"Select Top "&intArticles&" * From tblArticles WHERE published = 1 ORDER BY Adate DESC, Atime DESC;"
I dont want use ROWCOUNT
because its not working good it will not show some data of some fields
for example if I have Subject and Body fields, in results it does not show body content, and it will show just subject :confused:
this is my current code
CREATE PROCEDURE [dbo].[SP_Articles]
(
@Articles int
)
AS
SET ROWCOUNT @Articles
SELECT tblArticles.*
FROM tblArticles
WHERE published = 1 ORDER BY Adate DESC;
SET ROWCOUNT 0
GO
and I use this code to call that from ASP
strSQL = "EXECUTE SP_Articles @Articles = " & intArticles
rsArt.CursorType = 1
rsArt.Open strSQL, strCon
so just tell me how to use TOP in stored procedure
Thanks
View 9 Replies
View Related
Jan 30, 2004
Hi everybody,
How can I select records from a SP?
For example:
My SP is the following:
CREATE PROCEDURE [dbo].[spExample]
AS
Declare @SELECT_Text AS varchar(500)
Set @SELECT_Text='SELECT * FROM dbo.ExampleTable'
exec(@SQL_Text)
GO
I want to select from it:
SELECT *
FROM dbo.spExample
It doesn't work. Then how can I use the result of the SP???
View 2 Replies
View Related
Mar 16, 2004
Hey all! I have a quick question. Is it possible do formulate a select query where one of the tables is a recordset returned from a stored procedure? If so, what's the syntax?
In other words, if I have a stored proc whose last command is:
selectt.[Region_Code],
t.[Country_Code],
sum([Total]) as [Total]
from#tmp t
group by[Region_Code],
[Country_Code]
order by[Region_Code],
[Country_Code]
Then can I somehow get a handle on that cursor and join it to another table etc? Thanks!
View 5 Replies
View Related
Sep 19, 2013
I have a stored procedure that has many select statements in it. I want to call and execute the procedure in my c# code and put data returned by each select statement into separate grids. I could just write the select into the c# code and then execute as a dataset making that dataset the datasource for whatever grid but that defeats the purpose of stored procedures. Can I capture the data returned by each select in the stored procedure and have it put into a grid when the c# code is ran.
View 4 Replies
View Related
Jul 20, 2005
HelloI have many different tables, that most have the same structure.. they allhave some primary key, some fields and mylinkid ... so what I want to makeis to create a stored procedure, where I can pass the table-name, primarykey, and the new mylinkid, what should happen is, that the procedure copiesthe entrie, where i passed the id, to a new row and the mylink-id should getthe new value...example:mytable 1id=1 (primary key autoincrement)mylinkid=233field1=asdffield2=blablai call my procedure like: duplicate_entry 'mytable',1,4the result should then beid=2 (primary key/autincrement)mylinkid=4field1=asdffield2=blablaI was thinking to make a Stored Procedure, that copies the entier row with aselect * into mytable (SELECT * from mytable where id=1) and afterwards iupdate the mylinkid ..... but there i have problem with the primary key.The other solution that will work, but I won't like is to get with thesp_fields all fields from that specific table, build the select into withthe correct fields without the id-field... that should work, but was askingmyself if there is somethign better...Has someone a good idea for that?Thanks
View 6 Replies
View Related
Jul 14, 2006
I have code simililar to the following in a stored procedure:
CREATE PROCEDURE GetGroupMembers
(
@GroupID INT
)
AS
SELECT TMembers.MemberID, VCurrentMemberGroups.GroupID, THonorsMembers.HonorID
FROM THonorsMembers LEFT OUTER JOIN
TMembers ON THonorsMembers.MemberID= TMembers.MemberID LEFT OUTER JOIN
VCurrentMemberGroups ON TMembers.MemberID= VCurrentMemberGroups.MemberID
WHERE (VCurrentMemberGroups.GroupID = @intGroupID) AND TMembers.DeleteDate IS NULL
ORDER BY TMembers.MemberID
RETURN
GO
When I call this stored procedure in Query Analyzer, it takes two minutes to execute. The web pages that rely on it time out before that. If I copy just the select statement and substitute the group I'm working with for @GroupID, it takes less than a second for the statement to run and display results.
Can anyone tell me why a select statement called through a stored procedure would take minutes longer than one ran from Query Analyzer? I have many other stored procedures that are just select statements like this with a variable or three and they have no problems completing execution in a timely manner.
View 3 Replies
View Related
