I'm looking for a tool that can schedule stored procedures or queries and save the results to pdf or excel (preferrably both). SQL Server 2000, so 2005 reporting services are out, though if that has the capability I'd like to know because that might convince some of the powers that be to upgrade.
View 4 RepliesI'm new to Integration Services and going through the learning challenges one often faces when employing unfamiliar technology. I've created a simple package that executes a parameterized stored procedure and returns the results (multiple rows) into an object variable. How do I export the results to an XLS file? Do I have to use a script task to do this?
Regards,
Orlanzo
Goal: To run a stored procedure and export its results to Excel.
Challenge: The stored procedure produces multiple result sets. We want to programmatically export each result set into just 1 Excel file but into different Worksheets. Ex: result set 1 into Worksheet 1, result set 2 into Worksheet 2 and so on. Tools like
BCP utility and OPENROWSET are not enabled for use on the server. So those are out of the league for this one.After I run "EXEC [sp_select_ view_ columns_ from_pp]" -- Within the Results tab of Microsoft SQL Server Management Studio, I see:
viewName columnName columnDataType columnLength column
test test_id varchar 8 0
viewName columnName columnDataType columnLength column
test1 test1_id varchar 8 0
viewName columnName columnDataType columnLength column
test2 test2_id varchar 8 0
As you can see from above, that stored procedure sp_select_view_columns_from_pp is producing multiple result sets within the same Results tab in Microsoft SQL Server Management Studio. We want to make it so it exports to Excel into its own worksheets.
Code:
ALTER PROCEDURE [dbo].[sp_select_view_columns_from_pp]
AS
DECLARE @object_id INT;
DECLARE cur CURSOR FOR SELECT object_id
[code]....
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View Relatedhow can i refer to the results of a query inside a stored procedure?
and use them later
Hi I've run into a slight problem with my stored procedure.If I fire a query in a stored procedure, how can I access the results of that query in the stored procedure itself ?? ALTER PROCEDURE GetDetailsOfWebsiteUserForMail @request_id intAS declare @k int select person_id from Website_user w, Request r where r.user_id=w.user_id and r.request_id=@request_id set @k =person_id /* What do I do here??, value returned is unique */ if (@k!=0) /* Exists */ begin /* do something */ end else /* entry on person table does not exist */ begin /* do something */
end
I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.
Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...
INSERT INTO #datacompare(col1,col2)
EXEC mystoredprocedure
... but I can not seem to figure out how to dynamically gather the schema of the result set.
I can create 1 view of a database then use that views results to query
but i want do this with a stored procedure
pass some data to it, so it selects some data
query the results it created and output this data.
ie
pass 2 values to the procedure
(
@FirstValue int,
@secondValue int output
)
select from a database with the firstpassed value
Select *
From TableName
Where ID = @FirstValue
Then
using the results from the above select
Select *
From theResultsOfAbove
Where ID = @SecondValue
any ideas would be fabo !
Hi,
I am trying to create a DTS package that uses a sql stored procedure to generate a set of results and export those results to an excel spreadsheet on a server.
The trick is that the stored procedure accepts a parameter for Bank_Number (there are 10 of them). Therefore i was wondering if there was a way to somehow create the package to run the stored proc 10 times, each with a different bank number as the parameter and generate 10 different excel spreadsheets, one for each bank with it's results.
Can this be done using DTS or do i have to try another method?
thanks
scott
I have been trying to find a way to export the results of a query to a csv for use in excel but cannot.
View 2 Replies View RelatedHi,I am using a .dqy file to import some rows from a sql server 2003database to microsoft excel. Unfortunately, I have am importing morerows than excel can handle. Is there a way around this?It appears that there is no equivalent of the LIMIT(offset, number ofrows to return) function of mysql in tsql. Had there been anequivalent, I would have created multiple .dqy files, which would querythe database for pages of results as appropriate. There is an upperlimit to the number of records that there can be in the database, sothis would have been a feasible solution.Also, I must use .dqy files (or something equivalen) because thequeries are invoked from a website, and it is necessary to download theresults, sort/filter, etc. (in excel).Thanks for any suggestions.
View 4 Replies View RelatedHi all,
I am just wondering if anyone has a code to send query results to a spreadsheet package eg. MS Excel.
i) Automatically send the query result(s) to the package
ii) open the spreadsheet package;
Thanks.
Hi,
In SQL Server,
does anybody provide the steps for create a Package for exporting the query results to Excel environment?
I just know that click the "Export", then choose the query file to export to Excel.
I want to know how to create the Package to export it.
Please let me know, thanks.
Is there a way to export query results to an excel fie and add that file as an attachment in the email? All this has to be done using SQL query and it needs to be automated. My coworker tried using Openrowset and BCP, but it is not working.
View 3 Replies View RelatedHi All,
I have two SQL queries that we would like to automate. Ideally we want them to both be scheduled to run and dump their results to a single Excel spreadsheet with two workbooks, one for each query
Is it possible to do this? If not, sending each query to a seperate XLS or CSV file would be OK
Here are the queries:
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, p21_view_unvouched_po_currency_report.po_line_number, po_line.created_by, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report, P21.dbo.po_line po_line
WHERE po_line.po_no = p21_view_unvouched_po_currency_report.po_no AND po_line.line_no = p21_view_unvouched_po_currency_report.po_line_number
and
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, contacts.last_name, p21_view_inventory_return_hdr.buyer_id, p21_view_unvouched_po_currency_report.po_line_number, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.contacts contacts, P21.dbo.p21_view_inventory_return_hdr p21_view_inventory_return_hdr, P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report
WHERE p21_view_inventory_return_hdr.return_number = p21_view_unvouched_po_currency_report.unvouched_document_no AND contacts.id = p21_view_inventory_return_hdr.buyer_id
Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Relateddatagrid. Sorry, I'm new to it. For the few times I've done datagrids in the past, I've built
my own selects and filled the grid. This time I need to use someone elses stored procedure.
I simply don't know how to get the results back into my VB datagrid. Can someone point
me in the right direction? Thanks.
How do I filter the results of a stored procedure?
View 2 Replies View RelatedHi
I am trying to execute a stored procedure (which returns multiple tables) and use these results to populate an Excel file. I am totally lost on how to capture the results and use it. Any help will be appreciated.
Thanks
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."
Here is my Query,
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
reconfigure
SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Project](
[ProjID] [varchar](300) NOT NULL,
[ProjType] [varchar](20) NULL,
[ProjectTitle] [varchar](max) NULL,
[ProjectDetails] [varchar](max) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](max) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED
(
[ProjID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]
-----------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Report](
[ReportName] [varchar](50) NOT NULL,
[ProjID] [varchar](300) NULL,
[DeptCode] [varchar](50) NULL,
[ProjType] [varchar](50) NULL,
[ProjectTitle] [varchar](500) NULL,
[ProjectDetails] [varchar](3000) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](50) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED
(
[ReportName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]
--------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectStatus](
[ProjectStatusID] [int] IDENTITY(1,1) NOT NULL,
[ProjectStatus] [varchar](max) NULL,
CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED
(
[ProjectStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-----------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectManager](
[ProjectManagerID] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[Inactive] [int] NULL,
CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED
(
[ProjectManagerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
And here is the stored procedure that I wrote (doesn't return results): SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetReportQuery]
(
@ReportName varchar(100)
)
AS
BEGIN
SET
NOCOUNT ON
IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN
DECLARE @DeptCode varchar(50), @ProjID varchar(50)
SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName
SET @ProjID = @DeptCode + '-' + '%'
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName))))
AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL)
AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL)
AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL)
AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL)
AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL)
AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL)
)
END
END Can someone see what's wrong? Thanks.
I am having trouble getting data back from my stored procedure insert commands. Here is what I currently have set up.
1 - A dataset called YagDag
2 - A Table adapter called tadUsers
3 - A Stored Procedure that the tadUsers uses to insert called spUser_i
It seems like the way I have my VB code set up I can only retrieve a return int. I can't figure out how to get my User GUID back, any help would be really appreciated
Public Shared Function AddUser(ByVal EMail As String, ByVal FirstName As String, ByVal LastName As String, ByVal Password As String, ByVal EMailActive As Boolean) As Integer
Dim strEMail = AppFunction.SQLSafeString(EMail)
Dim strFirstName = AppFunction.SQLSafeString(FirstName)
Dim strLastName = AppFunction.SQLSafeString(LastName)
Dim strPassword = AppFunction.SQLSafeString(Password)
Dim blnEMailActive = EMailActive
Dim Users As New YagDagTableAdapters.tadUsers
Dim guidUserYID As Guid = Users.Insert(strFirstName, strLastName, strPassword)
Return 1
End Function -- =============================================
-- Author:Greg Moser
-- Create date: 3/29/2008
-- Description:Adds a User to the tblUser
-- =============================================
ALTER PROCEDURE [dbo].[spUser_i]
@FirstName varchar(50),
@LastName varchar(50),
@Password varchar(16)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserYID uniqueidentifier
SET @UserYID = NEWID()
-- Add User to tblUsers
INSERT INTO tblUsers (
YID,
Password,
FirstName,
LastName
)
VALUES (
@UserYID,
@Password,
@FirstName,
@LastName
)
--Return Users YID
SELECT @UserYID
END
Hi,
Is there a way I can display the data returned from more than one stored procedure in a report using reporting services. Or can I use more than one dataset in the report????
Two questions - one slightly off topic:
1) How do I write a stored procedure in MS SQL so that it returns a results set?
2) How do I get this into ASP.NET so that I can put the values into controls?
Jags
I wrote a stored procedure with three variables to be passed as input parameters. The stored procedure runs a select into statement into a temp table. The resulting temp table with another table was queried using right outer join to produce the desired results.
The stored procedure compiles error free.However when I ran the stored procedure with the parameters(3) in ISQL/W (SQL Server 6.5) the headers(column-names) were displayed but no records.
When runned as a query with the same parameter values, records were produced.
Help please urgent.
Regards
Olutimi Ooruntoba
I'm testing some code to look up values from my database and update a specific field when certain conditions are met. I'm having trouble with some code that is giving me the results I expect when I submit one set of parameters, but is not finding anything in the database for another set, when I know the data exists.
Here's the code for my stored procedure, SP:
Code:
@flightid bigint,
@departuretime smalldatetime
SELECT flightid, flightno, departuretime, origincode, destinationcode
FROM flightschedules
WHERE flightid <> @flightid
AND departuretime = CONVERT(SMALLDATETIME, @departuretime, 120)
And here's the vbscript that calls it:
Code:
vOutboundID = 452
vReturnID = 453
'--- Get the flight details ---
strOrigin = "confirmflightdetails '" & vOutboundID & "';"
set rsOrigin = Server.CreateObject("ADODB.Recordset")
rsOrigin.Open strOrigin, objConn
response.write "origin " & rsOrigin("departuretime") & "<BR>"
strReturn = "confirmflightdetails '" & vReturnID & "';"
set rsReturn = Server.CreateObject("ADODB.Recordset")
rsReturn.Open strReturn, objConn
response.write "return " & rsReturn("departuretime") & "<BR>"
strGetOFID = "SP '" & vOutboundID & "', '" & rsOrigin("departuretime") & "';"
set rsOFID = Server.CreateObject("ADODB.Recordset")
rsOFID.Open strGetOFID, objConn
DO WHILE NOT rsOFID.EOF
response.write "OFNO " & rsOFID("flightno") & " " & rsOFID("flightid") & "<br>"
rsOFID.MoveNext
Loop
strGetRFID = "SP '" & vReturnID & "', '" & rsReturn("departuretime") & "';"
set rsRFID = Server.CreateObject("ADODB.Recordset")
rsRFID.Open strGetRFID, objConn
DO WHILE NOT rsRFID.EOF
response.write "RFNO " & rsRFID("flightno") & " " & rsRFID("flightid") & "<br>"
rsRFID.MoveNext
Loop
Here's the code for confirmflightdetails:
Code:
@flightid bigint
AS
SELECT flightid, flightno, departuretime
FROM flightschedules
WHERE flightid = @flightid
When confirmflightdetails is tested, I the proper results, as confirmed by the response.write statements:
4521092006-07-29 08:00:00
4531102006-07-29 12:05:00
I put the response.write statements and loops in so I could verify the functionality.
Here's what it produces:
out 452
ret 453
origin 7/29/2006 8:00:00 AM
return 7/29/2006 12:05:00 PM
OFNO 109 450
Here's what it should produce:
out 452
ret 453
origin 7/29/2006 8:00:00 AM
return 7/29/2006 12:05:00 PM
OFNO 109 450
RFNO 110 451
If I do this in query analyzer:
Code:
select flightid, flightno, departuretime
from flightschedules
where flightid > 449 and flightid < 454
this is what I get from the database:
flightid flightno departuretime origin destination
4521092006-07-29 08:00:00 A C
4501092006-07-29 08:00:00 A B
4531102006-07-29 12:05:00 C A
4511102006-07-29 13:15:00 B A
What I'm trying to do is look up the chosen flight, then find the flight with the matching origin/destination (the other flight leg) on the same day.
I can't figure out why it's working for one set of parameters and not for the other.
Thanks in advance for any help!
Hi,
I'm trying to write a nested stored procedure, with the outer proc named "spAssociate", and inner proc named "spSales".
So far I have created the inner proc
CREATE PROCEDURE spSales@userID intASSET NOCOUNT ON
SELECT SalesOppID
FROM Sales_Opportunities
WHERE SalesOppCurrentStatus NOT IN ('Sale Lost','Sales Closed','Sale Closed','Unqualified','Deferred','Dropped')
AND OppOwnerUserID = @userIDGO
This was successfully created. I wanted to use the return set in the outer proc, which I tried creating as:
CREATE PROCEDURE spAssociate(@userID int,
@containerType varchar(100),
@associateType varchar(100))AS SET NOCOUNT ON
SELECT AssociateID
FROM AppRelations
WHERE ContainerType=@containerType
AND ContainerID IN (EXECUTE spSales @userID)
AND AssociateType=@associateType GO
I get an error "incorrect syntax near execute".
How can I use the results from the inner proc for the WHERE condition in my outer proc?
Any help is greatly appreciated. Thanks!
I'm sure this is an obvious question but much of SQL is new to me.I have a stored procedure and I want to use the results of a selectstatement elsewhere in the stored prcedure. The select statement willreturn at most one record and only one column, here's an example :select top 1 Sequence from MyTablewhere ParentId=4 and Sequence > 200 order by sequenceCan I put this result in a variable? Do I have to use SELECT INTOTempTable and refer to TempTable and then drop the table at the end ofthe stored procedure? I have read a little about cursors, are thesethe way to go?I'm confused as to what my options are, any help or links to help willbe appreciated.Thanks for reading.
View 1 Replies View RelatedHi,I created a VB SQL CRL Stored procedure for calculating a value. Valueis returned as belowUsing sConn4 As New SqlConnection("context connection=true")sConn4.Open()scmd = New SqlCommand("SELECT " & var_max, sConn4)sdrd = scmd.ExecuteReader()SqlContext.Pipe.Send(sdrd)End UsingWhen calling this stored procedure from a TSQL stored procedure forusing the value for further processing the value returned to myvariable is 0. The correct value should be 56. In results tab I get thecorrect result, but how can I assign it to my variable @max ?DECLARE @max1 intDECLARE @max intEXEC @max1 = [dbo].[VBSTP_calculate_MAX_no]@vsp_table_name = N'[dbo].[Message]',@vsp_table_key = N'message_no',@vsp_WHERE = N''print @max1 -- value here is 0SET @max = (SELECT @max1)Thanks a lot.
View 1 Replies View Relatedhi, hvae a little trouble with thos procedure.
When executed it always adds 2 to the value of fields Telefon and Mobil.
Can anyone help me, please
Kurt
ALTER Procedure opdaterbruger
(
@Initialer nvarchar(100),
@Mailadr nvarchar(100),
@Telefon float(8),
@PlusNet float(8),
@Mobil float(8),
@pnmobil float(8)
)
AS
UPDATE Brugere
SET Mailadresse = @Mailadr
WHERE Initialer = @Initialer
IF (@Initialer= '0') OR NOT EXISTS ( SELECT * FROM PNetlokal WHERE Bruger = @Initialer)
INSERT INTO pnetlokal ( Bruger, Kortnr, Telnr )
VALUES ( @initialer, @Plusnet, @Telefon)
ELSE
UPDATE PNetLokal
SET TelNr = @Telefon, KortNr = @PlusNet
WHERE Bruger = @Initialer
IF (@Initialer= '0') OR NOT EXISTS (SELECT * FROM PlusNet WHERE Bruger = @Initialer )
INSERT INTO plusnet (Bruger, Kortnr, Mobilnr)
VALUES (@initialer, @pnMobil, @mobil)
ELSE
UPDATE PlusNet
SET Mobilnr = @Mobil, KortNr = @pnmobil
WHERE Bruger = @Initialer
I need to return the results of a stored procedure into a temporary table. Something like this:
Select * into #temp from exec (the stored procedure). It appears that I can not do this.
The following will not work for me cause I am not sure how many columns will be returned. I want this to work even if the calling stored procedure is changed (i.e add or take away columns)
insert into (...) exec (the stored procedure.
Does anyone have any ideas how I could do this.
If I have multiple selects statements in one stored procedure. How do I access the different results in a report in Reporting Services? Where the first select is the detail lines of my report and the second select is just a field for a my header? Or am I going about this wrong with putting it all the results I want into one stored procedure for one report?
Example stored procedure:
ALTER PROCEDURE [dbo].[proc_DepositsByOfficer]
As
SELECT MASTER_DSC.APP
, MASTER_DSC.BRANCH
, qlkpOfficer.strName
, MASTER_DSC.DSC_OFFICER_49
, qlkpBranchName.strDescrS
, MASTER_DSC.DSC_CUR_BAL_21
, Case MASTER_DSC.APP WHEN 1 Then DSC_CUR_BAL_21 End AS DDA_BAL
, Case MASTER_DSC.APP WHEN 2 Then DSC_CUR_BAL_21 End AS SAV_BAL
, Case MASTER_DSC.APP WHEN 3 Then DSC_CUR_BAL_21 End AS CD_BAL
, MASTER_DSC.DSC_INT_RATE_45
, Case When [DSC_CUR_BAL_21]>0 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End As ANN_EXP
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=1 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_DDA
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=2 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_SAV
, Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=3 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_CD
, Case MASTER_DSC.APP WHEN 1 Then 1 End AS DDA_Count
, Case MASTER_DSC.APP WHEN 2 Then 1 End AS SAV_Count
, Case MASTER_DSC.APP WHEN 3 Then 1 End AS CD_Count
, qlkpApplicationCode.strDescrS AS strApplCode
FROM
MASTER_DSC
INNER JOIN qlkpApplicationCode ON MASTER_DSC.APP=qlkpApplicationCode.dblReference
LEFT JOIN qlkpOfficer ON MASTER_DSC.DSC_OFFICER_49=qlkpOfficer.intID
LEFT JOIN qlkpBranchName ON MASTER_DSC.BRANCH=qlkpBranchName.dblReference
WHERE
MASTER_DSC.DSC_CUR_BAL_21<>0
ORDER BY
MASTER_DSC.BRANCH;
SELECT dbo.fBankName() AS BankName;
I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.
This is the stored procedure:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Lance Colton
-- Create date: 7/31/06
-- Description:Find the contest winner
-- =============================================
ALTER PROCEDURE [dbo].[AppcheckContest]
-- Add the parameters for the stored procedure here
@BeginDate datetime = '1/1/2006',
@EndDate datetime = '12/31/2006',
@SectionID int = 10,
@WinnerID int = 0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 1 @WinnerID = P.UserID
FROM cs_Posts P
WHERE P.PostDate BETWEEN @BeginDate AND @EndDate
AND P.SectionID = @SectionID
AND P.UserID <> 2100 -- I don't want to win my own contest...
AND SettingsID = 1000 -- This number could be different if i had more than one CS installed?
AND IsApproved = 1
ORDER BY NEWID() -- yes this is slow, but it works...
RETURN @WinnerID
END
It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.
Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.
Here's the code from the aspx codebehind file:Partial Class Contest
Inherits System.Web.UI.Page
Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click
Dim stuff As New System.Web.UI.DataSourceSelectArguments
SqlDataSource1.Select(stuff)
SqlDataSource1.DataBind()
lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString
End Sub
End Class
As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @WinnerID parameter, but instead it sets it to "WinnerID".
Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server">
<asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" />
<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar>
<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa %>"
SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
<SelectParameters>
<asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" />
<asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" />
<asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" />
<asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="lblWinnerID" runat="server" Text="???"></asp:Label>
</asp:Content>