Bind a Table to a StoredProcedure

Hi All
I am an RS Newbie - Please be gentle :-))
I have a table on an RS report
When the table is bound to a simple Select statement EG select * from
Customers
I can then further bind each control on the report to a field in the
recordset.
EG I select the relevant text box - then from the property sheet I select
from the list of available fields
in the 'Value' combo box on the property sheet
EG txtCustomer value in the property sheet = Fields!CustomerName.Value
Cool
But when I change the recordset for the table to a Stored Proc
I see no available fields when I select a Text box and try to bind it to a
value in the propertyy sheet
Infact rather than a list of available fields, all I can see is <Expression>
Don't know where to go from here - any suggestions appreciated
Many thanks
DenzilSometimes when you switch to a stored procedure it does not detect the field
list. Try the following. Hopefully one of these two will do the trick.
1. in the dataset view click on the refresh fields button (it is to the
right of the ... and looks like the refresh button from IE
2. Make sure the command type is stored procedure and just put in the name
of the stored procedure like this: MyStoredProcName
Not like this: Exec MyStoredProcName
After doing this then try #1 again.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil" <u18940@.uwe> wrote in message news:5c3160a2829f0@.uwe...
> Hi All
> I am an RS Newbie - Please be gentle :-))
> I have a table on an RS report
> When the table is bound to a simple Select statement EG select * from
> Customers
> I can then further bind each control on the report to a field in the
> recordset.
> EG I select the relevant text box - then from the property sheet I select
> from the list of available fields
> in the 'Value' combo box on the property sheet
> EG txtCustomer value in the property sheet = Fields!CustomerName.Value
> Cool
> But when I change the recordset for the table to a Stored Proc
> I see no available fields when I select a Text box and try to bind it to a
> value in the propertyy sheet
> Infact rather than a list of available fields, all I can see is
> <Expression>
> Don't know where to go from here - any suggestions appreciated
> Many thanks
> Denzil|||Hi Bruce
Many thanks for the swift reply
When I read the bit about changing the command type I thought that would be
it
But...sorry none of this worked <sigh>
When I change the Command Type to StoredProc and remove the 'exec' at the
front of the
query string then try and run it - it errors out and does not give me the
expected popup box where I would manually enter any parameters
The error is
"An error occurred whilst trying to retrieve the parameters in the query
rsGetAccountList @.ActiveStatus does not exist"
before, I used to have "exec rsGetAccountList @.ActiveStatus" in the dataset
view and had the command type as Text. With that setup I could run the query
in DataSet view and get my parameter popup box
Any more suggestions? :-))
Many thanks
Denzil
Bruce L-C [MVP] wrote:
>Sometimes when you switch to a stored procedure it does not detect the field
>list. Try the following. Hopefully one of these two will do the trick.
>1. in the dataset view click on the refresh fields button (it is to the
>right of the ... and looks like the refresh button from IE
>2. Make sure the command type is stored procedure and just put in the name
>of the stored procedure like this: MyStoredProcName
>Not like this: Exec MyStoredProcName
>After doing this then try #1 again.
>> Hi All
>> I am an RS Newbie - Please be gentle :-))
>[quoted text clipped - 23 lines]
>> Denzil|||Did you put this? rsGetAccountList @.ActiveStatus
If so, remove th @.ActiveStatus. Just put the name of the stored procedure.
RS automatically retrieves the parameter list from the stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil" <u18940@.uwe> wrote in message news:5c319c74a8f20@.uwe...
> Hi Bruce
> Many thanks for the swift reply
> When I read the bit about changing the command type I thought that would
> be
> it
> But...sorry none of this worked <sigh>
> When I change the Command Type to StoredProc and remove the 'exec' at the
> front of the
> query string then try and run it - it errors out and does not give me the
> expected popup box where I would manually enter any parameters
> The error is
> "An error occurred whilst trying to retrieve the parameters in the query
> rsGetAccountList @.ActiveStatus does not exist"
> before, I used to have "exec rsGetAccountList @.ActiveStatus" in the
> dataset
> view and had the command type as Text. With that setup I could run the
> query
> in DataSet view and get my parameter popup box
> Any more suggestions? :-))
> Many thanks
> Denzil
>
> Bruce L-C [MVP] wrote:
>>Sometimes when you switch to a stored procedure it does not detect the
>>field
>>list. Try the following. Hopefully one of these two will do the trick.
>>1. in the dataset view click on the refresh fields button (it is to the
>>right of the ... and looks like the refresh button from IE
>>2. Make sure the command type is stored procedure and just put in the name
>>of the stored procedure like this: MyStoredProcName
>>Not like this: Exec MyStoredProcName
>>After doing this then try #1 again.
>> Hi All
>> I am an RS Newbie - Please be gentle :-))
>>[quoted text clipped - 23 lines]
>> Denzil|||Thanks Bruce
yes that was the answer
Many many thanks for your assistance
I really appreciate it
have agreat day
Darren
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1|||No problem. Support is strong for stored procedures but some of what you
need to do is not intuitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil via SQLMonster.com" <u18940@.uwe> wrote in message
news:5c4128a5414d2@.uwe...
> Thanks Bruce
> yes that was the answer
> Many many thanks for your assistance
> I really appreciate it
> have agreat day
> Darren
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1sql

BETWEEN statement

I was wondering if someone can correct me on this. When I run this query I get no results displayed. By default, I made sure I used all the values from beginning to end to test it.

USE POS410
GO
SELECT SSN#, Hire_date
FROM Employee
WHERE Hire_date BETWEEN 1/1/1995 AND 12/31/2005
GO1/1/1995 is an arithmetic expression meaning "1 divided by 1 divided by 1995"

this will round off to zero

you will probably get some results if you write this --

WHERE Hire_date BETWEEN '1995-01-01' AND '2005-12-31'|||that did the trick! thank you

between operator

Hi,

I'm having problems using the between operator in a query statement.

Select *
from <mytable>
where date between @.date1 and @.date2

The date values with a hour specified, aren't returned. What is the approach you would recommend here?

Thx

EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @.fromDate + '00:00:00' and @.toDate + '23:59:59'

this seem to work, but I'm not sure if this is correct

Select *
from <mytable>
where date >= @.date1 and date < dateadd(d,1,@.date2)

or

Select *
from <mytable>
where date >= @.date1 and date < @.date2 +1

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

i had a similar problem which i did through the application side:

string weekdate =txtFrom.Text;

string daydate = System.Convert.ToDateTime(txtTo.Text).Add(System.TimeSpan.FromDays(1)).ToString();

and later...

TechnicianViewInboxDataAdapter.SelectCommand.CommandText = tempselecttechnician + " where status = 1 and TechnicianID = " + Session["UserID"].ToString() + "and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' and DepartmentID = " + Session["DepartmentID"].ToString();

this works as well.

the code ends up like this:

SELECT FirstName, LastName, Email, CallNumber, DepartmentName, UserName, Status, TechnicianID, CallLoggedDT, DepartmentID, CallOpenedDT, CallActionedDT, CallClosedDT FROM dbo.TechnicianInboxView where status = 1 and TechnicianID = 2and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' and DepartmentID = 2

|||

You can also use the datediff function and the statement would look like this.

Select * from <mytable> where

datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

@.Date can be varchar too and it can be in the format of "mm/dd/yy" also. Need not have the time factor.

|||

where datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

Would not recommend doing so in the terms of performace. You will never get index seek over the index for datefield column: it will always index scan at the best.

Best way to search for all records (Using a Case Statement in a SP)

I have a form with a dropdown or combo box, the user can select <All>
or pick a user name. If they pick a user name my where clause works
fine, buts what's the best way to write "Select All" if they choose
the <All
This is what I have so far, but I don't think I should be using the
LIKE operator.

WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
@.myqarep END
and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
@.myoffice END

thanks for your help!!On 13 Sep 2004 10:47:21 -0700, PaulMac wrote:

>I have a form with a dropdown or combo box, the user can select <All>
>or pick a user name. If they pick a user name my where clause works
>fine, buts what's the best way to write "Select All" if they choose
>the <All>
>This is what I have so far, but I don't think I should be using the
>LIKE operator.
>WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
>@.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END
>thanks for your help!!

Hi Paul,

You could use this:

WHERE tblCase.qarep = CASE @.myqrep
WHEN '<All>' THEN tblCase.qarep
ELSE @.myqrep
END
AND tblOffice.officecode = CASE @.myoffice
WHEN -1 THEN tblOffice.officecode
ELSE @.myoffice
END

An alternative that's a bit more work to create but that will probably
perform better is to write different versions of the query; use IF to
select which search arguments are set to All and which are set to a value
and then execute the correct version of the query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||PaulMac (paulmac106@.hotmail.com) writes:
> I have a form with a dropdown or combo box, the user can select <All>
> or pick a user name. If they pick a user name my where clause works
> fine, buts what's the best way to write "Select All" if they choose
> the <All>
> This is what I have so far, but I don't think I should be using the
> LIKE operator.
> WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> @.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END

Since it's a combo, LIKE appears to be a bit of overkill. The normal
procedure is to pass NULL when you want all:

WHERE (qarep = @.myqarep OR @.myqarep IS NULL)
AND (officecode = @.myoffice OR @.myoffice IS NULL)

But of course this works too:

WHERE (qarep = @.myqarep OR @.myqarep = '<All>')
AND (officecode = @.myoffice OR @.myoffice = -1)

At least as long as you don't localize the string...

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I will not suggest putting a CASE command in the WHERE clause, you may
just want to use "if" control statement to separate two code blocks
which will be more efficient at database level.

IF Boolean_expression
{ sql_statement | statement_block }
[
ELSE
{ sql_statement | statement_block } ]

paulmac106@.hotmail.com (PaulMac) wrote in message news:<1ee8a467.0409130947.4f608eba@.posting.google.com>...
> I have a form with a dropdown or combo box, the user can select <All>
> or pick a user name. If they pick a user name my where clause works
> fine, buts what's the best way to write "Select All" if they choose
> the <All>
> This is what I have so far, but I don't think I should be using the
> LIKE operator.
> WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> @.myqarep END
> and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> @.myoffice END
> thanks for your help!!|||Hi Hugo

That worked perfectly...Thank You!!

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<3e6ck0dsnb7m8pvq95ps7ceu2nu0lj353t@.4ax.com>...
> On 13 Sep 2004 10:47:21 -0700, PaulMac wrote:
> >I have a form with a dropdown or combo box, the user can select <All>
> >or pick a user name. If they pick a user name my where clause works
> >fine, buts what's the best way to write "Select All" if they choose
> >the <All>
> >This is what I have so far, but I don't think I should be using the
> >LIKE operator.
> >WHERE tblCase.qarep LIKE CASE @.myqarep WHEN '<All>' THEN '%' ELSE
> >@.myqarep END
> > and tblOffice.officecode LIKE CASE @.myoffice WHEN -1 THEN '%' ELSE
> > @.myoffice END
> >thanks for your help!!
> Hi Paul,
> You could use this:
> WHERE tblCase.qarep = CASE @.myqrep
> WHEN '<All>' THEN tblCase.qarep
> ELSE @.myqrep
> END
> AND tblOffice.officecode = CASE @.myoffice
> WHEN -1 THEN tblOffice.officecode
> ELSE @.myoffice
> END
> An alternative that's a bit more work to create but that will probably
> perform better is to write different versions of the query; use IF to
> select which search arguments are set to All and which are set to a value
> and then execute the correct version of the query.
> Best, Hugo

Best way to create dynamic update statement

In general, What is the best approach in creating a dynamic update
stored procedure, that can handle recieving varying input paramters and
update the approporiate columns.Depends on the requirements but one possibility is to use NULL
parameters to represent values that shouldn't be changed:

UPDATE YourTable
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3)
... etc
WHERE ...

--
David Portas
SQL Server MVP
--|||>> In general, What is the best approach in creating a dynamic update
stored procedure, <<

In general, building dynamic is a bad idea. It says that you don't
know what you are doing, so you are turning over control of the system
at runtime to any random user, present or future. SQL is a compiled
language, not like BASIC.|||what would be wrong with using:
UPDATE YourTable
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3)
... etc
WHERE ...

if i want to have one stored procedure to update a table.|||jw56...@.gmail.com wrote:
> if i want to have one stored procedure to update a(ny) table.

what would be wrong

--Strider|||jw56...@.gmail.com wrote:
> if i want to have one stored procedure to update a(ny) table.

what would be wrong

--Strider|||I think this is just some confusion over terminology. The term "dynamic
update" or "dynamic code" refers to code that references metadata
(usually table and column names) dynamically - elements of the code
being constructed at runtime. This is not generally good practice for
various reasons to do with performance, security, maintainability and
modular design. In your case however, no dynamic code is necessary.

--
David Portas
SQL Server MVP
--