Passing NULL to DataSet parmameter

Hey All,

I have a number Stored Procs that have been around for a while that pull the entire list, or if I pass an ID, will just the record for that ID like below.

I want to be able to use these querries to poplate Multi-Select parameter dropdowns. going to the Data tab and creating a new dataset, I put in the SP name, and close the window. I then go to the Red ! point to preview the data it prompts me for my ID parmaeter on my SP. In the dropdown list it has '<Null>' (no ' though). When I run it, it works fine and returns all of my records.

When I run the report, it errors saying I didn't pass a parm for ID. I go back to the data tab, and edit my DataSet hitting the elipse. I go to the 3 tab called parameters, and type the following I get the following errors:

@.ID = <Null> - ERROR - [BC30201] Expression expected

@.ID= - ERROR - Return statement in function must return a value

@.ID = Null --ERROR - Null constant not supported use System.DBNull instead

@.ID = System.DBNull -ERROR - [BCS30691] DBNull is a type in System and cannot be used in an expression

@.ID=System.DBNull.Value NO ERROR, but it does not return anything either. I also did a SQL Trace, and I can see that it doesn't even send the querry to the database.

Does anyone know another magic value I can pass to get this to work?

I am being a little stuborn, I know that I could just create new procs, and wrap up the null, but the more stuff you create the more you have to maintain, so I would prefer to reuse these.

Thanks in advance.

Eric Wild

PS: My company is moving from crystal reports to Reporting service, and Reporting services is Rocks! It is very intuitve, simple and straign forward. The formatting is easy with the table and the matrix control blows away the crosstab control in crystal. Also, I'm finding that because crystal was so un extendable, that I would spend hours shaping sql to get over it's blemishes, and hours shaping it in the report, only to sometimes reliaze that the proposed onetime hack wouldn't work, and have to start all over! So far with RSS any tips and tricks I have learned can very easily be applied to any report I work on! Aslo, I do mostly interanet web apps, and it is nice to dump my reports on the Report Server, and not worry about haing to create a web page, create a datasource and all the ansilary stuff to go along with it. The only thing I don't like is the name 'Roporting Services': It does not stick out too far in Google Searches like 'AJAX.NET' or 'ASP.NET'. Anyway kudoes to the Reporting Services team!

ALTER PROC [dbo].[spGetLaborRole]
@.ID INT = NULL
AS
BEGIN
SELECT ID, Descr
FROM dbo.LaborRole
WHERE ( (ID = @.ID) OR (@.ID IS NULL) )

Hello Eric,

Can you verify that in your report parameter definition (Report menu --> Report Parameters), the 'Allow null values' checkbox is selected for your ID parameter?

Jarret

|||

Jarret,

That worked!

I guess I didn't see them as being related. I think of report parameters as things that communicate with the ouside world, and not related to my internl querrires. I wouldn't want a prompt to the end user showing ID: NULL to run the report. I can see though there is a hidden check so it not for end users. Cool thanks!

Here is steps on how to fix this.

1) go to the data tab and select the elipse. select the parameters tab and delete the @.ID=... stuff I put in and close Window.

2) Go to the Layout tab, and from the menu select Report/Report Parameters...

3) a new Parameter is in the list to the left callled ID.

-Check Allow Nulls

- Check Hidden

-Verify Default Value NULL is bubbled in below.

Thanks again

Eic Wild

Passing multiple record sets from one procedure to another

I am not having any luck getting this to work. I am using SQL 2000/2005
(we have both at the moment).
I am writing a stored procedure with the intent of creating a temp
table and inserting records into it. This procedure gets its records
from another sp. So SP 1 will call SP2. SP2, however, is used by other
applications directly, and it returns 3 recordsets. In my SP1, I only
want the results of the 1st recordset. Here is the flow:
SP 1:
- creates temp table
- Calls SP 2 and inserts the results of the first returned recordset
into the temp table
- selects/exports from temp table, blah blah...
I'm not sure how to get the results of the 1st recordset (only). Any
ideas?
TimModify the inner procedure, where you add one more optional parameter. When
you call this from your
outer proc, make sure that the inner proc only returns whatever it should re
turn in that particular
case.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"sdwebguy" <sdwebguy@.gmail.com> wrote in message
news:1144339633.674358.270200@.e56g2000cwe.googlegroups.com...
>I am not having any luck getting this to work. I am using SQL 2000/2005
> (we have both at the moment).
> I am writing a stored procedure with the intent of creating a temp
> table and inserting records into it. This procedure gets its records
> from another sp. So SP 1 will call SP2. SP2, however, is used by other
> applications directly, and it returns 3 recordsets. In my SP1, I only
> want the results of the 1st recordset. Here is the flow:
> SP 1:
> - creates temp table
> - Calls SP 2 and inserts the results of the first returned recordset
> into the temp table
> - selects/exports from temp table, blah blah...
> I'm not sure how to get the results of the 1st recordset (only). Any
> ideas?
> Tim
>|||In this case, I could update both procedures, so that is what I did --
and that works great!
If there a way to handle this if I was not able to update the called
procedure?
Thanks,
Tim|||Without seeing the code, it is a little hard to comment, but this
sounds like you are still writing procedural code. Your description is
the way we would have used scartch tapes in the 1950's.
table [faking a scratch tape?] and inserting records [sic] into it.<<
You talk about "records" and not sets. You talk about a sequence of
procedure calls, just like a 3GL program. SQL is declarative; we do
things in a single statement whenever possible -- or without a
statement at all (i.e. VIEWs -- very handy and always up-to-date)!
But ignoring the lack of declarative programming in your narrative,
your procedural programming is not good.
Let's get back to coupling and cohesion, basic software engineering,
etc. You have what I call a "Britany Spears, Squid and Automobiles"
procedure. Each result should be created by one well-defined, coherent
procedure. Get out your old copy of Yourdon & DeMarco. This is far
more basic than SQL programming.|||I appreciate your response and commentary. Forgive me for not being
perfect, nor following your coding style. Rehashing the original
question so I can use proper terminology for you is a waste of time for
all of us. I work with what I have inherited just like everyone else.
And by the way, she spells her name Britney.
All the best,
Tim|||>>Forgive me for not being perfect, <<
Perfect is nice, but we can aim for competent, standard, etc.
Actually, it is ISO-11179 and not me. Then there is the whoel Yourdon,
DeMarco, et al stuff and DoD-2176 rules.
NO! If you use the wrong mental model, you will NEVER really get the
fundations of RDBMS. Words are concepts. We deal in a world of
abstractions -- the wrong words mean the wrong concepts. Do you
understand the problem with no having a "zero" and a "nothing", "null"
and "empty set" concepts in your math? HONKING BIG DIFFERENCE, UNH?
No, someone created the mess in the first place. God did not make Bade
Databases on the 8-th day, or even the 9-th. The real problem is that
peopel do nto fix them later (and kill the SOB who started the mess). .
I try to web-surf porno sites with girls who are more age-appropriate
to me; too bad most fo them are dead now). Except Ann-Margaret
(google it, kid).|||> If there a way to handle this if I was not able to update the called
> procedure?
If an outer proc calls an inner proc and that inner proc returns one result
set, you can do below in
the outer proc:
INSERT INTO ...
EXEC innerProc
But you can only catch the first result set from int inner proc this way. Th
e other result sets will
be returned to the client.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sdwebguy" <sdwebguy@.gmail.com> wrote in message
news:1144689399.872944.186380@.v46g2000cwv.googlegroups.com...
> In this case, I could update both procedures, so that is what I did --
> and that works great!
> If there a way to handle this if I was not able to update the called
> procedure?
> Thanks,
> Tim
>|||I'm sorry--did you say something? All I heard was crap. You may sound
smart--even may be smart--but your tone and communication style really
makes you look like an idiot.|||ignore celko. he can't really help anyone.

Passing in query paramater values through WebUserControls

Hi,

I don't want to pass in parameter values in the url to get a certain record from my database (for a better search engine result) so instead i'm using webuser controls (this is amust) with Public Values that should be read by the SqlDataSource. So in the page that contains the usercontrol there is something like:

UserControl.Value = "myvalue"

Now I want the SqlDataSource in the usercontrol to read this value and pass it into a query (in the parameter of the WHERE statement, eg.: @.recordID = myvalue).

Thanks in advance!

Hi,

You should create a public attribute in your user control(eg. below). When using the user control, regard it as a input parameter. In this way ,the parameter can be passed to the SqlDataSource in the user control.

private string _para1;public string para1{get{return _para1; }set{ _para1=value; }}

Thanks.

Passing a specific cursor record to a function

Hello,

Is it possible? Can I select a specific record of the cursor to be
sent to a seperate function to do all the computations etc.?

Regards,
VSHi

You will have to pass each as a separate variable or possibly use a
(temporary) table. If you can rewrite the cursor to be a set function you
will usually get much better performance.

John

"TinTin" <lalalulu24@.yahoo.com> wrote in message
news:2d5425d1.0406151234.3925efae@.posting.google.c om...
> Hello,
> Is it possible? Can I select a specific record of the cursor to be
> sent to a seperate function to do all the computations etc.?
> Regards,
> VS|||TinTin (lalalulu24@.yahoo.com) writes:
> Is it possible? Can I select a specific record of the cursor to be
> sent to a seperate function to do all the computations etc.?

You can pass a cursor varible to stored procedure, but I am not sure that
functions accept cursor variables.

In any case, cursors is not something you should use that often. As I said
in my other posting, work set-based whenever possible.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
John: I think I will buy the Temporary Table suggestion. Thanks!

Erland: I am not too sure about what you mean by "work set-based".
There might be an easier alternative to what I am doing. Could you
precisely refer to me a specific area; or topic which I should read?

Regards!

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns950A2ABF8BDEYazorman@.127.0.0.1>...
> TinTin (lalalulu24@.yahoo.com) writes:
> > Is it possible? Can I select a specific record of the cursor to be
> > sent to a seperate function to do all the computations etc.?
> You can pass a cursor varible to stored procedure, but I am not sure that
> functions accept cursor variables.
> In any case, cursors is not something you should use that often. As I said
> in my other posting, work set-based whenever possible.|||Hi

You would have to post the DDL (Create table statements etc), Example Data
as Insert statements and expected output along with your stored procedure
definition, so that we have a better idea what you are trying to do.

John

"TinTin" <lalalulu24@.yahoo.com> wrote in message
news:2d5425d1.0406160504.66185536@.posting.google.c om...
> Hi
> John: I think I will buy the Temporary Table suggestion. Thanks!
> Erland: I am not too sure about what you mean by "work set-based".
> There might be an easier alternative to what I am doing. Could you
> precisely refer to me a specific area; or topic which I should read?
> Regards!
>
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
news:<Xns950A2ABF8BDEYazorman@.127.0.0.1>...
> > TinTin (lalalulu24@.yahoo.com) writes:
> > > Is it possible? Can I select a specific record of the cursor to be
> > > sent to a seperate function to do all the computations etc.?
> > You can pass a cursor varible to stored procedure, but I am not sure
that
> > functions accept cursor variables.
> > In any case, cursors is not something you should use that often. As I
said
> > in my other posting, work set-based whenever possible.|||TinTin (lalalulu24@.yahoo.com) writes:
> Erland: I am not too sure about what you mean by "work set-based".
> There might be an easier alternative to what I am doing. Could you
> precisely refer to me a specific area; or topic which I should read?

Rather than writing:

DECLARE @.price money,
@.qty int,
@.total money,
@.orderid int,
@.prev_orderid int

DECLARE order_total_cur INSENSITIVE CURSOR FOR
SELECT orderid, price, qty
FROM order_details
ORDER BY orderid

OPEN order_total_cur
SELECT @.total = 0
WHILE 1 = 1
BEGIN
FETCH order_total_cur INTO @.orderid, @.price, @.qty
IF @.@.fetch_status <> 0
BREAK

IF @.prev_orderid IS NOT NULL AND @.orderid <> @.prev_orderid
BEGIN
UPDATE orders
SET total = @.total
WHERE orderid = @.prev_orderid

SELECT @.total = 0
END

SELECT @.total = @.total + @.price * @.qty, @.prev_orderid = @.orderid
END

DEALLOCATE order_total_cur

IF @.orderid IS NOT NULL
BEGIN
UPDATE orders
SET total = @.total
WHERE orderid = @.orderid
END

You write:

UPDATE orders
SET total = od.total
FROM orders o
JOIN (SELECT orderid, total = sum(qty * price)
FROM orderdetails
GROUP BY orderid) AS od ON o.orderid = od.orderid

Not only is this more concise and less error-prone to write, the
difference in performance could be magnirute if there are many
rows in the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Pass record to user-defined function and create xml

Does anyone know if there is a way to pass a record from a select to a
user defined function in SQL Server 2000? The number and names of the
columns in the record will vary depending on the upload temp table
selected from... You should be able to see what I am trying to
accomplish bellow... If you have another idea, I'm open to
suggestions.
Example:
Declare @.CurrentFiscalYear smallint
Set @.CurrentFiscalYear = 2005
Create table ClaimEditLog(
TransactionId int,
XMLData varchar(7500),
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Create Table #tbClaimUploadData(
TransactionId int IDENTITY (1, 1) NOT NULL ,
FiscalYear smallint,
AmountTypeId int,
Amount money
)
Insert #tbClaimUploadData
Select 2005, 2, 556.98
Insert #tbClaimUploadData
Select 2006, 2, 56.90
Insert into ClaimEditLog
Select TransactionId,
dbo.UDF_ConvertRecordToXML(*),
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
>From #tbClaimUploadData
Where FiscalYear <> @.CurrentFiscalYear
-- UDF_ConvertRecordToXML - Convert record to XML
Select * from ClaimEditLog
--Output
2,
'<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalYea
r><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>',
'The Fiscal Year is incorrect.',
2006-05-31 06:41:32.527You could store the complete record and use a computed column to give a XML
representation of the output...
So your table definition would be this...
Create table ClaimEditLog(
TransactionId int,
FiscalYear smallint,
AmountTypeId int,
Amount money,
XMLData AS
'<XMLData><Record><TransactionId>'
+ CAST( TransactionId as varchar(20) )
+ '<FiscalYear>' + CAST( FiscalYear AS char(4) ) + '
etc...',
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Your insert would be...
insert claimeditlog (
TransactionId,
FiscalYear,
AmountTypeId,
Amount,
EditDesc,
LastUpdateId,
LastUpdate )
select TransactionId,
FiscalYear,
AmountTypeId,
Amount,
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
from #tbClaimUploadData
Select * from ClaimEditLog
And you'd get the XMLData output as text XML.
Make sense?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<porsch55@.yahoo.com> wrote in message
news:1149077706.631013.220560@.y43g2000cwc.googlegroups.com...
> Does anyone know if there is a way to pass a record from a select to a
> user defined function in SQL Server 2000? The number and names of the
> columns in the record will vary depending on the upload temp table
> selected from... You should be able to see what I am trying to
> accomplish bellow... If you have another idea, I'm open to
> suggestions.
> Example:
> Declare @.CurrentFiscalYear smallint
> Set @.CurrentFiscalYear = 2005
> Create table ClaimEditLog(
> TransactionId int,
> XMLData varchar(7500),
> EditDesc varchar(200),
> LastUpdateId varchar(20),
> LastUpdate datetime
> )
> Create Table #tbClaimUploadData(
> TransactionId int IDENTITY (1, 1) NOT NULL ,
> FiscalYear smallint,
> AmountTypeId int,
> Amount money
> )
> Insert #tbClaimUploadData
> Select 2005, 2, 556.98
> Insert #tbClaimUploadData
> Select 2006, 2, 56.90
> Insert into ClaimEditLog
> Select TransactionId,
> dbo.UDF_ConvertRecordToXML(*),
> 'The Fiscal Year is incorrect.',
> 'jporscha',
> GetDate()
> Where FiscalYear <> @.CurrentFiscalYear
> -- UDF_ConvertRecordToXML - Convert record to XML
> Select * from ClaimEditLog
> --Output
> 2,
> '<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalY
ear><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>'
,
> 'The Fiscal Year is incorrect.',
> 2006-05-31 06:41:32.527
>