multi-statement functions vs stored procedures

I would like to understand the reason in which to "use multi-statement
functions instead of stored procedures that return tables. Writing stored
procedures that return tables as multi-statement userdefined functions can
improve efficiency."
When to use Multi-statement table-value and when to use stored procedure?
Thanks.
In general:
When you want to execute code, use stored procedures.
When you want to use the result table of your code in a SELECT statement (in the FROM clause), use a
MSTVUDF.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:3EA76526-4DA5-454B-973E-F457D7795E30@.microsoft.com...
> I would like to understand the reason in which to "use multi-statement
> functions instead of stored procedures that return tables. Writing stored
> procedures that return tables as multi-statement userdefined functions can
> improve efficiency."
> When to use Multi-statement table-value and when to use stored procedure?
> Thanks.
>

multi-statement functions vs stored procedures

I would like to understand the reason in which to "use multi-statement
functions instead of stored procedures that return tables. Writing stored
procedures that return tables as multi-statement userdefined functions can
improve efficiency."
When to use Multi-statement table-value and when to use stored procedure?
Thanks.In general:
When you want to execute code, use stored procedures.
When you want to use the result table of your code in a SELECT statement (in the FROM clause), use a
MSTVUDF.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:3EA76526-4DA5-454B-973E-F457D7795E30@.microsoft.com...
> I would like to understand the reason in which to "use multi-statement
> functions instead of stored procedures that return tables. Writing stored
> procedures that return tables as multi-statement userdefined functions can
> improve efficiency."
> When to use Multi-statement table-value and when to use stored procedure?
> Thanks.
>sql

Multiplication Aggregate

Howdy folks. I have a question about how to go about creating a function that will return the multiplication aggregate over a row. Essentially I need this to function in the exact same was as the sum() function, but with multiplication instead. I've been struggling trying to create a UDF to do this for me, but I'm not having much luck. I would be amazed if this is not something somebody else has already done out of necessity. Any ideas?Maybe you can show what formula you want to apply?|||Ok, how about:

USE Northwind
GO

CREATE FUNCTION udf_MULT (@.x float)
Returns float
AS
BEGIN
DECLARE @.y float
SELECT @.y = @.x * 2
RETURN @.y
END

GO

SELECT Freight, dbo.udf_MULT(Freight) FROM Orders
GO|||Brett, i don't think your udf_MULT is what Teddy wants

sample rows:

name value
foo 2
bar 4
qux 6
fap 8

select sum(value) from ... gives 20

select mult(value) from ..., assuming there were such an aggregate function as "mult()", would return 384

maybe a variation of that routine written up in that sqlteam article using coalesce to produce a comma-delimited list?

rudy|||You mean like:

USE Northwind

DECLARE @.x money
SELECT @.x = ISNULL(@.x,1) + Freight FROM Orders
SELECT @.x

I added them because of overflow...but you could use * instead...|||mmm, i love overflow

perhaps that's why "mult()" was never invented -- too easy to blow up the query real good

:)|||Add for the +, the ISNULL value should be 0, not 1...1 For Multiplication

hmmmmmmmmm...my cup runneth over with a flaming homer...or was that a flaming moe...

Hey...seeing it in type...

Noooow I get it...

Great episode...

Got to pick me up some cough medice on the way home...|||Thanks for the reply guys!!

I've been a bit busy with the holiday so I haven't had much time to make my rounds here. So far I've learned that this essentially is not possible with anything other then ints without a udf. Currently I'm searching for a udf that will do this for me, as I am not terribly familiar with coding them myself. Perhaps it's time to up the learning curve eh?|||a MULT function for varchar?

Hmmmm, now you've got me TOTALLY lost...

What exactly are you trying to do?|||Originally posted by Brett Kaiser
a MULT function for varchar?

Hmmmm, now you've got me TOTALLY lost...

What exactly are you trying to do?

Not for varchar, float. The closest I've found so far is sort of a bizarre workaround that doesn't seem to work properly over large recordsets:

EXP(SUM(LOG(value)))|||Originally posted by Brett Kaiser
What exactly are you trying to do?

Do you mean like what r937 said?

You can't (to my knowledge) create a user defined scalar function...

But you can employ the other method I posted

DECLARE @.x bigint
SELECT @.x = @.x + floatColumn FROM Table
SELECT @.x

Is that what you're trying to do? Multiply all the numbers in 1 column?|||Originally posted by Brett Kaiser
Do you mean like what r937 said?

You can't (to my knowledge) create a user defined scalar function...

But you can employ the other method I posted

DECLARE @.x bigint
SELECT @.x = @.x + floatColumn FROM Table
SELECT @.x

Is that what you're trying to do? Multiply all the numbers in 1 column?

That appears to be returning null. Does the select @.x = iterate through the entire recordset? I'm a bit new with variable usage in the queries themselves. Generally I would do this sort of thing at the application level with my front-end.|||Give this a read...

http://sqlteam.com/item.asp?ItemID=2368|||That appears to be returning nullhence the need for the COALESCE

and yes, you did mention this earlier, brett, but it's worth repeating -- 0 for addition, 1 for multiplication

:D|||Originally posted by r937
hence the need for the COALESCE

and yes, you did mention this earlier, brett, but it's worth repeating -- 0 for addition, 1 for multiplication

:D

Ok, I'm think I'm getting on the right track, but I am still not quite getting what I need. Currently I am using:

DECLARE @.x FLOAT

SELECT @.x = COALESCE(@.x, 1) * myField
FROM myTable

SELECT @.x

That is now returning 0.0 Oddly enough, if I use + instead of *, I return the same as SUM() + 1. So it does appear to function exactly as expected when applied to addition (the +1 is the result of using 0 as opposed to 1 for the null return), I'm still a bit stumped as to why it returns zero for multiplication. What am I overlooking?|||overlooking? probably precision and/or scale (i can never remember which is which)

try

SELECT @.x = COALESCE(@.x, 1.000000000) * myField|||Originally posted by r937
overlooking? probably precision and/or scale (i can never remember which is which)

try

SELECT @.x = COALESCE(@.x, 1.000000000) * myField

The issue was an overflow error. I ran the same syntax over a much smaller dataset and produced the desired result.

Thanks a bunch guys!!

This is at least enough to get me over the "how the hell..." hump that everyone hits once in a while.

Much obliged.|||And just for another twist...what if 1 of your rows has a 0

Come on...

puuuuleeeeeeeeeeeeze

Tell us this is more than an academic exercise...because I can't think of a practical application at all...|||Originally posted by Brett Kaiser
And just for another twist...what if 1 of your rows has a 0

Come on...

puuuuleeeeeeeeeeeeze

Tell us this is more than an academic exercise...because I can't think of a practical application at all...

The practical application is to a series of modifiers to an insurance quote. If one of them is zero, then the quote is free. I doubt that will happen.

:)|||So you have a very specific predicate...the quote...how many modifiers per quote on average?

...and if it can happen it will happen...just a matter of time...

CREATE TABLE myTable99 (Col1 int NOT NULL CONSTRAINT myTable99_chk1 CHECK (Col1 <> 0))|||Originally posted by Brett Kaiser
So you have a very specific predicate...the quote...how many modifiers per quote on average?

...and if it can happen it will happen...just a matter of time...

CREATE TABLE myTable99 (Col1 int NOT NULL CONSTRAINT myTable99_chk1 CHECK (Col1 <> 0))


approx 7-8 modifiers per quote. The mods tend to be in the .75 - 1.5 range. And it will never be 0. Not unless the chairman of the board owes someone a serious favor. In which case, that's really not my problem to begin with.

The issue is the varying number of modfiers for each quote. Some will only have one, some will have 8. That's why this mult function comes in handy. This is fairly easy to do client-side, but for some reason is murderous to do in sql until this morning.|||So you would check to see if the result of this multiplier function is zero, and then act accordingly?

There are other methods that might work. For instance:

select min(Abs(YourColumn))

...would return zero if any of the values is zero.

blindman|||...and you can include this directly in a calculation:

Cost * cast(cast(min(Abs(YourColumn)) as bit) as int)

...would result in a cost of $0 if any of the multipliers is zero.

blindman|||Originally posted by blindman
...and you can include this directly in a calculation:

Cost * cast(cast(min(Abs(YourColumn)) as bit) as int)

...would result in a cost of $0 if any of the multipliers is zero.

blindman

That's really not an issue. This is well and solved.

Anywho, the current method would return 0 as-is.|||Originally posted by Teddy
Not for varchar, float. The closest I've found so far is sort of a bizarre workaround that doesn't seem to work properly over large recordsets:

EXP(SUM(LOG(value)))

I do like the workaround solution. Could you tell me what do you mean by "doesn't seem to work properly over large recordsets"? Is it the overflow problem? I tested it on a table of 4000 records, the results is as expected.|||Originally posted by shianmiin
I do like the workaround solution. Could you tell me what do you mean by "doesn't seem to work properly over large recordsets"? Is it the overflow problem?

edit: read that wrong.

I'm not sure what the issue is. It returns nothing at all when I use a large set. Very odd.|||Originally posted by Teddy
edit: read that wrong.

I'm not sure what the issue is. It returns nothing at all when I use a large set. Very odd.

for actual cases, i.e.

approx 7-8 modifiers per quote. The mods tend to be in the .75 - 1.5 range. And it will never be 0. Not unless the chairman of the board owes someone a serious favor.

I think this would be a best solution to me. :)

select
case
when min(isnull(f1, 0))=0 then 0
else exp(sum(log(isnull(case when f1=0 then 1 else f1 end,1))))
end
from t1

The query posted handles zero and null value correctly.
And I don't think overflow would be a problem.
(There shouldn't be negative values in the table, if it is, you need to add some code to handle it properly)|||The issue is:

USE Northwind
GO

DECLARE @.x BIGINT
SELECT @.x = 1
--Wont Blow Up
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 1
SELECT @.@.ERROR AS ErrorCode
--Will Blow Up
SELECT @.x
SELECT @.x = 1
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 2
SELECT @.@.ERROR AS ErrorCode
SELECT @.x

--The error should read:
-- Server: Msg 8115, Level 16, State 2, Line 11
-- Arithmetic overflow error converting expression to data type bigint.

In any event, error checking is a must...for all code...|||Originally posted by Brett Kaiser
The issue is:

USE Northwind
GO

DECLARE @.x BIGINT
SELECT @.x = 1
--Wont Blow Up
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 1
SELECT @.@.ERROR AS ErrorCode
--Will Blow Up
SELECT @.x
SELECT @.x = 1
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 2
SELECT @.@.ERROR AS ErrorCode
SELECT @.x

--The error should read:
-- Server: Msg 8115, Level 16, State 2, Line 11
-- Arithmetic overflow error converting expression to data type bigint.

In any event, error checking is a must...for all code...

Since the query returns float, the range would be - 1.79E + 308 through 1.79E + 308, that's what I meant that overflow shouldn't be a problem for actual situration. To prevent errors of overflow, the query may check the value before applying EXP(), that would be something like

case
when sum(log(...)) > 307 then ... -- overflow
when sum(log(...)) < -307 then ... -- underflow
else exp(sum(log(...)))
end|||Ahhh proactive error handling...very nice...

just watch...the [banging head]overhead[/banging head] for some statements...|||Originally posted by Brett Kaiser
Ahhh proactive error handling...very nice...

just watch...the [banging head]overhead[/banging head] for some statements...

I agree that the overhead should be considered. Normally procedural statements have more control over what needs to be done while combination of functions tends to make code look neat. Which choice to go would depends on actual siturations. :)

Multiple Zip Search

I'm heaving a heck of a time to get a sql query working. I'm being passed a series of zip codes and I need to return a list of all the rows that match those zip codes in a database of about 40,000 leads.

Can someone clue me in to what the commandtext of my query should look like?

Thanks!

EXEC( 'SELECT * FROM YourTable where YourZip IN (''' + @.z + ''')' )

where @.z = list of zip codes.

hth|||Well if you are inclined to avoid D-SQL and are using SQL Server 2000, you can use a UDF
or many other techniques which would prove much better than D-SQL.|||My problem is that I'm a web guy with just enough SQL 2000 experience to be dangerous. I'm not exactly sure that the first answer given would help me because the zip codes are different for every query. I have a list of zip codes and I have a parameter set up in my dataadapter (@.zip), but I can't get any results. It's probably an issue with single or double quotes, but i'm not sure. I'm putting all the zip codes together into a session variable, trying to get something like this:

WHERE ZIP LIKE '%80000%' OR ZIP LIKE '%72322%' etc. There can be up to 75 zips in one query.

I really appreciate the help!|||A user defined function to create an query with IN ('zip,zip,etc') is your best bet.

Here is someting I wrote a long time ago but it was similar to what you could do.

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = N'fnConvertListToStringTable')
DROP FUNCTION [dbo].[fnConvertListToStringTable]
GO
CREATE FUNCTION [dbo].[fnConvertListToStringTable] (
@.list varchar(1000)
)
RETURNS @.ListTable table (ItemID varchar(250) NULL)
AS
BEGIN
IF(SUBSTRING(@.list,LEN(@.list),1) <> ',')
BEGIN
SET @.list = @.list + ','
END
DECLARE @.StartLocation int
DECLARE@.Length int
SET @.StartLocation = 0
SET @.Length = 0
SELECT @.StartLocation = CHARINDEX(',',@.list,@.StartLocation+1)
WHILE @.StartLocation > 0
BEGIN
IF(LOWER(RTRIM(LTRIM(SUBSTRING(@.list,@.Length+1,(@.StartLocation-@.length) -1))))) = 'null'
INSERT INTO @.ListTable(ItemID) VALUES (Null)
ELSE
BEGIN
INSERT INTO @.ListTable(ItemID)
VALUES (RTRIM(LTRIM(SUBSTRING(@.list,@.Length+1,(@.StartLocation-@.length) -1))))
END
SET @.Length = @.StartLocation
SET @.StartLocation = CHARINDEX(',', @.list, @.StartLocation +1)
END
RETURN
END
GO
select * from dbo.fnConvertListToStringTable(' yjyj, 1345, 1234,NUll')

The code above actually put the values in a table and then used a where in (Select from that table) but in your case you can juse use the part that does the parsing at the beginning.sql

Multiple view from a single select statement

Hi,
I want to write an SQL query which should return me 2 kinds of outputs depending on which condition is true. i.e.

the query should be something like

select someview if (condition1 = true)
else select someotherview if (condition2 = true)

where someview is a set of columns from one table only, and someotherview is a set of columns which is a superset of someview (& is generated from two tables, which have no common field)

Let me explain this further --
I want to select data from one table and a single field from other table, however if I the first table does not return any data, I still want data from the other table to be returned.
Can I write a SQL query to do this?

-- Amitdo a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?|||Originally posted by edwinjames
do a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?

I know, that I can use a union, however I want to support TimesTen & Oracle using thsame query...TT does not allow union while oracle does, can I write another query which emulates union?|||I am sorry I am not farmiliar with TT.

Does it support outer joins?|||Originally posted by edwinjames
I am sorry I am not farmiliar with TT.

Does it support outer joins?

Yes it dows, hence I am now using outer joins|||Glad to be of help

:)

Multiple Values Returned from Stored Proc

I need to return 2 values from a stroed proc. Can you have more than one
output parameters in a stored proc? If not, would bringing back a dataset b
e
better than two round trips to the database to get the 2 values I need?
--
Robert HillRobert wrote:
> I need to return 2 values from a stroed proc. Can you have more than
> one output parameters in a stored proc? If not, would bringing back
> a dataset be better than two round trips to the database to get the 2
> values I need?
Output variables are generally faster to return than generating a 1 row
result set. You can have more than one output parameter in a procedure.
I would tell you to determine if, in fact, the results should be
returned as a resultset or as output parameters. If you think that
additional values may need to be returned in the future or if you think
there might be a time when more than one row needs to be returned, it's
better to use a resultset so you don't have to mess with the interface
of the procedure.
David Gugick
Imceda Software
www.imceda.com|||I am using the Microsoft Application Block for DataAccess and I cannot find
a
suitable procedure to call to bring back two output parameters. If this is
correct, I will need to extend the Application Block to include a procedure
to do what it is I need. Is this correct?
"David Gugick" wrote:

> Robert wrote:
> Output variables are generally faster to return than generating a 1 row
> result set. You can have more than one output parameter in a procedure.
> I would tell you to determine if, in fact, the results should be
> returned as a resultset or as output parameters. If you think that
> additional values may need to be returned in the future or if you think
> there might be a time when more than one row needs to be returned, it's
> better to use a resultset so you don't have to mess with the interface
> of the procedure.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Robert wrote:
> I am using the Microsoft Application Block for DataAccess and I
> cannot find a suitable procedure to call to bring back two output
> parameters. If this is correct, I will need to extend the
> Application Block to include a procedure to do what it is I need. Is
> this correct?
>
I plead ignorance. I have never used the Microsoft Application Block for
DataAccess. I don't know how the object model looks. I assume it's a
high-level view of ADO.Net. Where do you define parameters for the
stored procedures. Make sure you're using the latest release, now called
the "Enterprise Library Patterns and Practices Library"
http://msdn.microsoft.com/library/d...li
b.asp
David Gugick
Imceda Software
www.imceda.com

Multiple Selects in a Stored Proc

I need to perform 2 selects in a stored procedure, but only return data from the second..

eg.

first select gets the proper table to search from a transaction summary table

select category from trans summary where transsummary.transid = @.transid

based on the category returned something like this

if category = 1
then tabletosearch = table1
end if
if category = 2
then tabletoseach = table 2
endif

select * from @.tabletosearch


Is this possible, or am I going about this the wrong way?...
As you probably can tell I am new at this.
Thanks folksTry something like...

select @.category = (select category from trans summary where transsummary.transid = @.transid)

if @.category = 1
then @.tabletosearch = table1
end if
if @.category = 2
then @.tabletoseach = table 2
endif

select * from @.tabletosearch|||DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||That worked great if I used SELECT * but as soon as I try to use only specific fields.....

Originally posted by achorozy

DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||I'm sorry if the example I showed was inline with your question, after all your question did have 'SELECT *'.

Since you don't tell us why it doesn't work when you list out the columns my only guess is that each table has different columns.

So why not just do this:

DECLARE @.category int

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SELECT col1, col2, col3 FROM table1

if (@.category = 2)
SELECT colA, colB, colC FROM table2
Sorry if I sound a bit off, but I've never asked a question and all of my 200+ posting has come from answering question, trying too anyways. So I get tired after awhile when someone says "It does work".
What doesn't work? What error did you get? Why doesn't it work?|||My apologies, but if it isnt obvious already, I am new to SQL and these newsgroups..again my apologies..

So far I am getting to about 7/8ths of the way thru want I need to do.
SO I will do, what I should have done the first time. give all the details

Here is what I want, I am able to get it to the first portion to work (select if...) so I won't go into more detail on that.

I need to be able to get data from 3 tables and only certain fields from each table

so

select IDCode.t1, IdDetail.t1, SalesRep.t1, SalesRep1Addr.t2, SalesRepEmail.t2, OfficeId.t2, OfficeAddr.t3, OfficeEmail.t3

S.. t1 contains a unique id from t2, and t2 contains a unique id from t3...

and obviously the the corresonding Sales Rep Detail & Office Detail...

Thanks so much for the help

Multiple selection in a paramter?

I need a report (VS2005) that will select multiple records, such as:
Select * from CrimCase where Docket in (?). There is already code to
return a string of Dockets (pretty complex) so it would be easier to
just pass that along than to try to duplicate the code in SQL.
I tried setting the Report Parameter to Multi-Value but that doesn't
work. I have no idea how to pass in the parameter but this does work:
Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
When I replace the string with ? and get prompted for a parameter, it
does not work.
Any suggestions appreciated.On Feb 20, 9:31 am, dgk <d...@.somewhere.com> wrote:
> I need a report (VS2005) that will select multiple records, such as:
> Select * from CrimCase where Docket in (?). There is already code to
> return a string of Dockets (pretty complex) so it would be easier to
> just pass that along than to try to duplicate the code in SQL.
> I tried setting the Report Parameter to Multi-Value but that doesn't
> work. I have no idea how to pass in the parameter but this does work:
> Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
> When I replace the string with ? and get prompted for a parameter, it
> does not work.
> Any suggestions appreciated.
If you are referring to linking a multi-value parameter to a stored
procedure, you will want to select the Data tab >> select Edit
Selected Dataset [...] >> select the Parameters tab >> set Parameter
Name = @.Docket and set Parameter Value to an expression similar to
this: =Join(Parameters!Docket.Value, ","). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Wed, 20 Feb 2008 18:51:47 -0800 (PST), EMartinez
<emartinez.pr1@.gmail.com> wrote:
>On Feb 20, 9:31 am, dgk <d...@.somewhere.com> wrote:
>> I need a report (VS2005) that will select multiple records, such as:
>> Select * from CrimCase where Docket in (?). There is already code to
>> return a string of Dockets (pretty complex) so it would be easier to
>> just pass that along than to try to duplicate the code in SQL.
>> I tried setting the Report Parameter to Multi-Value but that doesn't
>> work. I have no idea how to pass in the parameter but this does work:
>> Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
>> When I replace the string with ? and get prompted for a parameter, it
>> does not work.
>> Any suggestions appreciated.
>
>If you are referring to linking a multi-value parameter to a stored
>procedure, you will want to select the Data tab >> select Edit
>Selected Dataset [...] >> select the Parameters tab >> set Parameter
>Name = @.Docket and set Parameter Value to an expression similar to
>this: =Join(Parameters!Docket.Value, ","). Hope this helps.
>
Not exactly, since it isn't going to a stored procedure; the SQL is
text. But, I didn't know about that parameter tab, nor that I could
put in a more complex expression. I'm not sure how to interact with
the SSRS engine. I really want the query to end up constructing OR
statements - ie, Select * from LawCases where Docket = 1112222 or
docket = 444232 or docket = 777333, extending the query depending on
the actual number of docket numbers passed in the one parameter.
I think a more acceptable way is to just create a temporary table,
putting in the cases that I want reported on, and then call SSRS,
taking all the cases in that table.
I'm intrigued by what I can do with the code though. Is it possible to
write code in the Custom Code section that will actually construct the
query on the fly, or is that code only for calling once the query has
returned and the records are being processed?|||First, it looks like you are using ODBC (hence the ? in your query). No
problem, just that when you map query parameters to report parameters it is
order dependent (i.e. the order your ? come in your query).
The following query will work for you:
Select * from LawCases where Docket in (?)
Then in layout, Report Menu-> Report Parameters set this parameter as
multi-value.
For testing, put in the appropriate values in available values. Do not put
quotes (single or double). Make sure the data type of the parameter is
string.
Put this in for available values:
Label Value
Case1 2006NY031095
Case2 2006NY024091
After you get this working then add whatever parameters you need to have
another dataset that creates this list of dockets. For instance, add your
date range and the other dataset uses the data range to return the dockets.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:912rr3hcfe43pt8ri4fm580tanniajmhd3@.4ax.com...
> On Wed, 20 Feb 2008 18:51:47 -0800 (PST), EMartinez
> <emartinez.pr1@.gmail.com> wrote:
>>On Feb 20, 9:31 am, dgk <d...@.somewhere.com> wrote:
>> I need a report (VS2005) that will select multiple records, such as:
>> Select * from CrimCase where Docket in (?). There is already code to
>> return a string of Dockets (pretty complex) so it would be easier to
>> just pass that along than to try to duplicate the code in SQL.
>> I tried setting the Report Parameter to Multi-Value but that doesn't
>> work. I have no idea how to pass in the parameter but this does work:
>> Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
>> When I replace the string with ? and get prompted for a parameter, it
>> does not work.
>> Any suggestions appreciated.
>>
>>If you are referring to linking a multi-value parameter to a stored
>>procedure, you will want to select the Data tab >> select Edit
>>Selected Dataset [...] >> select the Parameters tab >> set Parameter
>>Name = @.Docket and set Parameter Value to an expression similar to
>>this: =Join(Parameters!Docket.Value, ","). Hope this helps.
> Not exactly, since it isn't going to a stored procedure; the SQL is
> text. But, I didn't know about that parameter tab, nor that I could
> put in a more complex expression. I'm not sure how to interact with
> the SSRS engine. I really want the query to end up constructing OR
> statements - ie, Select * from LawCases where Docket = 1112222 or
> docket = 444232 or docket = 777333, extending the query depending on
> the actual number of docket numbers passed in the one parameter.
> I think a more acceptable way is to just create a temporary table,
> putting in the cases that I want reported on, and then call SSRS,
> taking all the cases in that table.
> I'm intrigued by what I can do with the code though. Is it possible to
> write code in the Custom Code section that will actually construct the
> query on the fly, or is that code only for calling once the query has
> returned and the records are being processed?

multiple select statements

Hi guys and gals,

I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:

'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'

My Code is below:

//Start of sql

CREATE PROCEDURE ADMIN_GetSingleUsers
(
@.userID int
)
AS

DECLARE @.userSQL int
SET @.userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
Users.Unit_ID As usersUnitID,
*
From TITLE, Users
WHERE
User_ID = @.userID AND
TITLE.TITLE_ID = Users.Title_ID )

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

//End of sql

Can you point to what I am doing wrong? Thanks in advance!

You are trying to SET @.userSQL to more than one value (User_ID, Title.Title, Users.Active, Users.Approved, and Users.Unit_ID).

Try it in one statement instead, something like this:

SELECT
Units.Unit_ID,
Units.Parent_ID,
Units.Unit_Name
FROM
Units
INNER JOIN
Users ON Units.Unit_ID = Users.Unit_ID AND Users.User_ID = @.UserID
INNER JOIN
Title ON Users.Title_ID = Title.Title_ID

|||

Depends on if you wanted both result sets to be returned or just the second one.

return both:

SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
@.userSQL=Users.Unit_ID As usersUnitID,
*
From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

return second one:

SELECT @.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

Or if you don't need @.userSQL except for limiting the second query:

SELECT Unit_ID, Parent_ID, Unit_Name@.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

JOIN UNITS ON (Users.Unit_ID=UNITS.Unit_ID)

WHERE User_ID = @.userID

|||

Another Question.

Is there a way to do the following?

DECLARE unitID nVarChar(255)
SET @.unitID = (Select * From Units)

and give @.unitID the exact value from *?
Or loop throug the @.unitID and get the value I need?

|||Declare it as a table?

Multiple Return Values

I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)

Right now, I use the statement "return @.@.Identity" for a single value, but there is another variable assigned in the procedure, @.NewCounselingRecordID that I need to pass back to the calling class method.

I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.

However, I feel this solution is kludgy. Is there a more correct way to accomplish this?

Thanks in advance for your comments.check out BOL for OUTPUT parameters..

hth|||Yes, that helps...I can't believe I brain farted on output parameters. (Duh :P)

Thanks

Multiple return

Hi,
I have one stored procedure with multiple selects, like this:
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
Now it returns nothing, I suspected the count values.
I tried it with return... but then I only get one value.
How can I get the values as a table? With NumUsers and NumPages as
columnnames?
Thanks!hi,
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
SELECT @.NumUsers As NumUsers, @.NumPages As NumPages
"Arjen" <boah123@.hotmail.com> wrote in message
news:ddpuls$upt$1@.news1.zwoll1.ov.home.nl...
> Hi,
> I have one stored procedure with multiple selects, like this:
> SELECT @.NumUsers = COUNT(*) ...
> SELECT @.NumPages = COUNT(*) ...
> Now it returns nothing, I suspected the count values.
> I tried it with return... but then I only get one value.
> How can I get the values as a table? With NumUsers and NumPages as
> columnnames?
> Thanks!
>|||Simple... ;-)
Thanks!
"arik" <arikf@.top4.com> schreef in bericht
news:%23qLX56YoFHA.2080@.TK2MSFTNGP14.phx.gbl...
> hi,
> SELECT @.NumUsers = COUNT(*) ...
> SELECT @.NumPages = COUNT(*) ...
> SELECT @.NumUsers As NumUsers, @.NumPages As NumPages
>
> "Arjen" <boah123@.hotmail.com> wrote in message
> news:ddpuls$upt$1@.news1.zwoll1.ov.home.nl...
>|||Hi
It would be better to return them as output parameters as there will be less
overhead in doing so. See the topic "Returning Data Using OUTPUT Parameters"
in Books online.
John
"Arjen" wrote:

> Simple... ;-)
> Thanks!
>
>
> "arik" <arikf@.top4.com> schreef in bericht
> news:%23qLX56YoFHA.2080@.TK2MSFTNGP14.phx.gbl...
>
>|||You can use OUTPUT parameters or a simple resultset.
CREATE PROCEDURE dbo.foo1
AS
BEGIN
DECLARE @.NumUsers INT, @.NumPages INT
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
SELECT NumUsers = @.NumUsers, NumPages = @.NumPages
END
GO
CREATE PROCEDURE dbo.foo2
AS
@.numPages INT OUTPUT,
@.numUsers INT OUTPUT
BEGIN
SELECT @.NumUsers = COUNT(*) ...
SELECT @.NumPages = COUNT(*) ...
END
GO
Using the resultset is more code in the procedure (and more expensive
resource-wise) but simpler to deal with in the caller. Using Output
parameters makes the procedure itself smaller and lighter, but is a bit more
cumbersome to code from the caller.
A
"Arjen" <boah123@.hotmail.com> wrote in message
news:ddpuls$upt$1@.news1.zwoll1.ov.home.nl...
> Hi,
> I have one stored procedure with multiple selects, like this:
> SELECT @.NumUsers = COUNT(*) ...
> SELECT @.NumPages = COUNT(*) ...
> Now it returns nothing, I suspected the count values.
> I tried it with return... but then I only get one value.
> How can I get the values as a table? With NumUsers and NumPages as
> columnnames?
> Thanks!
>

Multiple Resultset limit?

Is there a limit to the number of result sets that a SQL query can return at one time?Can a query return more than one result set ? Or, do you mean is there a limit to the number of rows in a result set ?

Cheers
Martin|||Yes, I know that a single query can return multiple result sets.

I'm asking if anyone knows if the number of result sets a single query can return has a limit.

I had a stored procedure that returned something like 23 result sets, and I had to split it up into two seperate stored procedures that each returned half the result sets because my code would error trying to access the last ones. My code as in VB6 so there could just be a limit to the number of result sets from a single query that VB6/ADO can handle.|||Is it possible that some of your result sets contain identical column names? I'm wondering if there is some conflict with the DataAdapter TableMappings?

Martin|||Like I said, this was a problem I was experiencing with VB6/ADO, so there's no DataAdapter like in .NET.