OUTPUT and Recordset

I have created a 'Intelligent' search stored procedure that accepts two input parameters SearchFor and SearchIn and an OUTPUT parameter @.Result

Basically it starts by looking for a match for the SearchFor and if more than 1 is found, returns a recordset of the matches so the user can select a SearchFor category, at the same time the OUTPUT parameter is set to let the calling script know what is being returned.

Likewise it does the same for the SearchIn until both are satisfied and the true search results recordset can be returned.

Is it possible to get an OUTPUT parameter AND a recordset at the same time?

Any help will be appreciated,

PhlazarI would put the results of the stored procedure into a global temp table....

Cheers
C

Output a report directly to PDF?

Hey everyone, does anyonw know how to output a report directly to PDF?
That is, without passing any parameters, but defaulted in the report
itslef. So a user would not have a choice to view it in the report
manager?I think the only way to do this is to have your own frontend. No way to
configure the server to do this (I think it would be a bad idea overal, PDFs
are much much slower and more compute intensive). I agree for some reports
it would be nice to mark it as rendering only in a certain format. But, no
capability for that exists.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Daniel.P.Lyonnais@.lmco.com> wrote in message
news:1131047107.559743.200760@.f14g2000cwb.googlegroups.com...
> Hey everyone, does anyonw know how to output a report directly to PDF?
> That is, without passing any parameters, but defaulted in the report
> itslef. So a user would not have a choice to view it in the report
> manager?
>|||Daniel;
You can simply render the report as a PDF file as shown below. Will that do
what you need?
Wayne
=======================================Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim rs1 As New Wedding.rs.ReportingService
rs1.Credentials = New System.Net.NetworkCredential("myServer", "tmyPW", "")
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
' Render the report to HTML4.0
results = rs1.Render(Session("ReportPath"), "PDF", _
Nothing,
"<DeviceInfo><StreamRoot>/WebApplication1/</StreamRoot></DeviceInfo>",
Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
Response.BinaryWrite(results)
End Sub
<Daniel.P.Lyonnais@.lmco.com> wrote in message
news:1131047107.559743.200760@.f14g2000cwb.googlegroups.com...
> Hey everyone, does anyonw know how to output a report directly to PDF?
> That is, without passing any parameters, but defaulted in the report
> itslef. So a user would not have a choice to view it in the report
> manager?
>sql

Outer Join?

I am having troubles with a query that may have to use an Outer Join.
I would appreciate any help with this problem.
I will pass two parameters to the stored procedure.
@.OldStnType and @.NewStnType.
I want to see which records exist in @.OldStnType and not in
@.NewStnType. In this example:
set @.OldStnType='BUILD'
set @.NewStnType='SCHED'
Since only optionnbr 4 exists in Build and Not in Sched, I want to
return the record the following:
StnType OptionNbr
========== =============
BUILD 4
CREATE TABLE [dbo].[cfgStnOptionList] (
[StnType] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OptionNbr] [int] NOT NULL ,
[Description] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdated] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert into cfgStnOptionList values ('BUILD',3,'Station 1310
Option','Jan 2, 2005')
Insert into cfgStnOptionList values ('BUILD',4,'Test 4','Jan 1, 2005')
Insert into cfgStnOptionList values ('SCHED',1,'Marquee CommPort','Jan
1, 2005')
Insert into cfgStnOptionList values ('SCHED',2,'Scanner CommPort','Jan
1, 2005')
Insert into cfgStnOptionList values ('SCHED',3,'Station 12313','Jan 1,
2005')
Thank You,
Dave.Hi
These are untested, but try:
SELECT A.[StnType] , A.[OptionNbr]
FROM [dbo].[cfgStnOptionList] A
LEFT JOIN [dbo].[cfgStnOptionList] B ON A.[OptionNbr] = B.[OptionNbr] AND
B.[StnType] = @.NewStnType
WHERE B.[OptionNbr] IS NULL
AND A.[StnType] = @.OldStnType
Or
SELECT A.[StnType] , A.[OptionNbr]
FROM [dbo].[cfgStnOptionList] A
WHERE NOT EXISTS ( SELECT * FROM [dbo].[cfgStnOptionList] B WHERE
A.[OptionNbr] = B.[OptionNbr] AND B.[StnType] = @.NewStnType )
AND A.[StnType] = @.OldStnType
John
"Dave" wrote:

> I am having troubles with a query that may have to use an Outer Join.
> I would appreciate any help with this problem.
> I will pass two parameters to the stored procedure.
> @.OldStnType and @.NewStnType.
> I want to see which records exist in @.OldStnType and not in
> @.NewStnType. In this example:
> set @.OldStnType='BUILD'
> set @.NewStnType='SCHED'
> Since only optionnbr 4 exists in Build and Not in Sched, I want to
> return the record the following:
>
> StnType OptionNbr
> ========== =============
> BUILD 4
>
>
> CREATE TABLE [dbo].[cfgStnOptionList] (
> [StnType] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OptionNbr] [int] NOT NULL ,
> [Description] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastUpdated] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> Insert into cfgStnOptionList values ('BUILD',3,'Station 1310
> Option','Jan 2, 2005')
> Insert into cfgStnOptionList values ('BUILD',4,'Test 4','Jan 1, 2005')
> Insert into cfgStnOptionList values ('SCHED',1,'Marquee CommPort','Jan
> 1, 2005')
> Insert into cfgStnOptionList values ('SCHED',2,'Scanner CommPort','Jan
> 1, 2005')
> Insert into cfgStnOptionList values ('SCHED',3,'Station 12313','Jan 1,
> 2005')
> Thank You,
> Dave.
>|||That worked great John!
Thank you for your help.
Dave.
On Tue, 10 May 2005 04:36:02 -0700, John Bell
<jbellnewsposts@.h0tmail.com> wrote:
>Hi
>These are untested, but try:
>SELECT A.[StnType] , A.[OptionNbr]
>FROM [dbo].[cfgStnOptionList] A
>LEFT JOIN [dbo].[cfgStnOptionList] B ON A.[OptionNbr] = B.[OptionNbr] AND
>B.[StnType] = @.NewStnType
>WHERE B.[OptionNbr] IS NULL
>AND A.[StnType] = @.OldStnType
>Or
>SELECT A.[StnType] , A.[OptionNbr]
>FROM [dbo].[cfgStnOptionList] A
>WHERE NOT EXISTS ( SELECT * FROM [dbo].[cfgStnOptionList] B WHERE
>A.[OptionNbr] = B.[OptionNbr] AND B.[StnType] = @.NewStnType )
>AND A.[StnType] = @.OldStnType
>John
>"Dave" wrote:
>sql

OUT and OUTPUT - Stored procedure parameters.

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET-

--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @.Product varchar(40)

, @.MaxPrice money

, @.ComparePrice money OUTPUT

, @.ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice;

-- Populate the output variable @.ListPprice.

SET @.ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice);

-- Populate the output variable @.compareprice.

SET @.ComparePrice = @.MaxPrice;

GO

USE

DECLARE @.ComparePrice money, @.Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@.ComparePrice OUT,

@.Cost OUTPUT

IF @.Cost <= @.ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

-

Partial Result Set

-

--Product List Price

-

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.

Well, OUT and OUTPUT keywords are synonyms, just as INT and INTEGER are.|||

Really?

AAAAAAAARGH it's all clear now. I can't see why in the example they mix and match however though - it just brings confusion into the equation.

No point in answering the other part of my Q: I 'm sure the example iterates throught the execution of the SP, but doesn't show it.

Thank you Sergey.

OSQL Question

I created a sql script that transfers data from my test system to production adn vice versa. I would like to use OSQL and be able to pass parameters to the SQL script.

Example: param 1 = test; param 2 = production.

Can anyone tell me if this is possible and how to do it?

Thanks in advance for all the help.You can't directly pass parameters as part of the OSQL command-line utility, but you have several alternatives for solving your problem.

As stated in Winnet mag article :
OSQL won't let you pass in parameters, but you can use the sp_executesql stored procedure, which can process parameterized SQL. (For detailed information about sp_executesql, see SQL Server Books OnlineBOL.) The following example lets you pass parameters into a T-SQL command but doesn't completely address how to pass a parameter as part of an OSQL command-line session (remember that you must issue the OSQL command from a command prompt):

osql -E -MyServer -Q "EXECUTE sp_executesql N'SELECT *
FROM northwind..
orders WHERE OrderId = @.OrderId' ,N'@.OrderId int' ,@.OrderId = 10248"
Another solution relies on the power of Windows to handle the parameterization for you. For DBAs who aren't familiar with Windows-level command-file processing, a batch file is a text file that has a .bat extension. Windows treats batch files as executables that run in the Command Prompt environment. You can think of batch files as mini programs that Windows runs.

You can simply create a file called SQLVariableBatch.bat, and put the following text in it:

osql -E -MyServer -Q "SELECT *
FROM northwind..orders WHERE OrderId = %1"
From the directory where you saved the .bat file, issue the following command from a command prompt window:

SQLVariableBatch 10248
When running this command, Windows will replace the %1 in the SQLVariableBatch.bat file with what comes after the batch file's name in the command linein this case, 10248. This is a simple example of batch processing in Windows; to learn more about batch files, see the Windows Help files.

You can also use Windows Scripting Host (WSH) to manage the parameterization. Using WSH for scripting and batch processing is much more flexible and powerful than using simple Windows batch files. I'm not a WSH expert, so I don't include an example of this solution, but I wanted to note that the option exists. For information about using WSH, see the MSDN Web site for Windows Script at

http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28001169 .|||Thanks for your help. I will try your suggestions and see if I can figure it out.