forming where clause

I wonder if I could write a dynamic where statement in a store procedure.
I have a web page that will pass following information to the store procedur
e.
Lets say I have a parameter @.typeid that can be 1 for rent, 2 for sale, 1 or
2 for either. My store procedure looks like this
create select_apartment_details
(
@.typeid nvarchar(50) -- @.typeid ='1' or @.typeid ='2' or @.typeid='1, 2'
)
AS
SELECT * FROM tbl_apartments WHERE typeid IN (@.typeid)
GO
By the way typeid field in the tbl_apartments is integer.
However, this doesnt work. Can anyone help me pls?If the values in the TypeID Column in the tbl_apartments table are indeed
limited to
1 or 2, then I suggest the following
Pass in @.TypeID = 1 When you want only the 1s,
@.TypeID = 2 When you want only the 2s,
and @.TypeID = 3, when you want 1s and 2s
Then in your where clause, you can do this
Where TypeID ^ @.Type <> 0
This will do a bitwise And comparison such that
Database TypeID
@.TypeID | 1 2
-- | -- --
1 | 1 0
2 | 0 2
3 | 1 2
"regaliel" wrote:

> I wonder if I could write a dynamic where statement in a store procedure.
> I have a web page that will pass following information to the store proced
ure.
> Lets say I have a parameter @.typeid that can be 1 for rent, 2 for sale, 1
or
> 2 for either. My store procedure looks like this
> create select_apartment_details
> (
> @.typeid nvarchar(50) -- @.typeid ='1' or @.typeid ='2' or @.typeid='1, 2'
> )
> AS
> SELECT * FROM tbl_apartments WHERE typeid IN (@.typeid)
> GO
> By the way typeid field in the tbl_apartments is integer.
> However, this doesnt work. Can anyone help me pls?
>|||Sorry Memory tricked me... The bitwise operator yu want is bitwise AND, and
the operator symbol is an Ampersand as in
Where TypeID & @.TypeID <> 0
"regaliel" wrote:

> I wonder if I could write a dynamic where statement in a store procedure.
> I have a web page that will pass following information to the store proced
ure.
> Lets say I have a parameter @.typeid that can be 1 for rent, 2 for sale, 1
or
> 2 for either. My store procedure looks like this
> create select_apartment_details
> (
> @.typeid nvarchar(50) -- @.typeid ='1' or @.typeid ='2' or @.typeid='1, 2'
> )
> AS
> SELECT * FROM tbl_apartments WHERE typeid IN (@.typeid)
> GO
> By the way typeid field in the tbl_apartments is integer.
> However, this doesnt work. Can anyone help me pls?
>|||Try this:
SELECT * FROM tbl_apartments WHERE CHARINDEX(CAST(typeid AS CHAR(1)), @.test)
> 0
"regaliel" <regaliel@.discussions.microsoft.com> wrote in message
news:9B49F533-89B9-492D-B556-96B2199585C9@.microsoft.com...
>I wonder if I could write a dynamic where statement in a store procedure.
> I have a web page that will pass following information to the store
> procedure.
> Lets say I have a parameter @.typeid that can be 1 for rent, 2 for sale, 1
> or
> 2 for either. My store procedure looks like this
> create select_apartment_details
> (
> @.typeid nvarchar(50) -- @.typeid ='1' or @.typeid ='2' or @.typeid='1, 2'
> )
> AS
> SELECT * FROM tbl_apartments WHERE typeid IN (@.typeid)
> GO
> By the way typeid field in the tbl_apartments is integer.
> However, this doesnt work. Can anyone help me pls?
>

Formatting/Returning stored proc results as XML

I have a stored proc that contains a simple select statement. The select statement does not use the 'FOR XML' clause, nor can the select statement be modified to use the 'FOR XML' clause (for compatibility reasons)

Is there a way to execute the stored proc such that it returns its output in XML? I'm looking for something similar to this:

Code Snippet

exec spGetUserDetails @.ID = 1234 for xml raw

Thanks.

I found an approach that would preserve backwards compatibility while allowing me to manipulate results retrieved by a stored proc. It has to do with Table-valued user-defined functions:

Move the select statement from the stored proc into a user--defined function that returns TABLE:

Code Snippet

-- Example from Books Online

CREATE FUNCTION fn_SalesByStore (@.storeid int)
RETURNS TABLE
AS
RETURN ( SELECT * from Products where StoedID = @.storeid );

Modify the original stored proc to call this user-defined function, i.e., the stored proc wraps the UDF hence preserving backwards compatibility:

Code Snippet

-- Inside the stored proc comment the select statement and call the UDF

-- SELECT * from Products where StoedID = @.storeid -- No longer needed

select * from fn_SalesByStore (@.storeid);

Now I can call the UDF to retreive the same results that would have been retrieved by the original stroed proc, and more importantly, manipulate them as required.

Code Snippet

select * from fn_SalesByStore (@.storeid) for xml raw

Yazan

Formatting Problem

I would like to change the background of a cell if its value is 'Test'
I have tried this thru both custom code and just thru a simple iif statement both which produce errors. I put the following iif statemnt in

=iif(Fields!change_type.value="Test","Cyan","Red")

and get the following error:

c:\variset4\reports_microsoft\ToolPredByOp.rdl The background color expression for the textbox 'CHANGE_TYPE' refers to the field 'change_type'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Any suggestions?disregard my last message - my line was correct - the problem was that Reporting services is Case sensitive. I should have had my field names in all caps. I found a good ei was trying to do at:

http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

if anyone is interested...|||

Hi.....

May be ur Prblm is Caps letter..or U Can also try Like this....
=iif(Fields!change_type.value="Test","Cyan","Red")
As
use ur Text Box Name instead of Field value.... ok write this Code in BackGround -- <Expression...>

=iif(ReportItems!textbox1.value="Test","Cyan","Red")

Best Regards......

formatting output

Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----
SET NOCOUNT ON
DECLARE @.errorCount INT
SELECT @.errorCount = COUNT(*) FROM error_report WHERE id != - 2 AND id != - 5
IF @.errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
--------
The Results in the bottom pane looks like this below
------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
------
But, when I do a select, they are like this below
--

NODETABLE 0 There are 2 duplicate subclass name: Specification
Propertytable 0 at Parentid = 2000000859 and propertyid = 721
Propertytable 0 at Parentid = 10122 and propertyid = 9That sounds like a grid limitation to me. I'd suggest Ctrl-T to set text mode, then Ctrl-E to execute the query again.

-PatP|||Hi Pat,
It outputted in the text format but the results are the same. However when I do select statement, it is fine.|||You need to explicitly specify the size for VARCHAR fields.|||Hi LOOKING AT THE ABOVE CODE, CAN YOU TELL ME WHERE IT IS?|||For example, here:

'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR(8)) )

But you also don't need that CAST.

'The Report was generated on ' + CONVERT(VARCHAR(8), GETDATE(), 1)

Formatting Numbers in an SQL Statement

Hi,
I have a table that has an ID field which is automatically incremented as each new record is added, so if I do a SELECT * FROM Table1 I get:
ID, Name
1, Billy
2, Bob
3, Tony
You get the idea. What I want to do is format the number differently when it's returned from an SQL statement so I get:
ID, Name
0001, Billy
0002, Bob
0003, Tony
So I need something like SELECT FORMATNUMBER(ID, 4), Name FROM Table1 - Does anything like this exist?
Little 'un.This type of functionality is probably better served in your code, rather than on Sql. Sql is rather slow at doing string functions.
SELECT
RIGHT( '0000' + cast( ID as varchar), 4 ) ID
FROM
table1
bill|||

Hi Bill,

Thanks for that, unfortunately it's not something I can do in my ASP code, so that should work a treat.

Little'un

sql

Formatting a Select Statement

Hi everyone!
I am looking for a function similar to the Left(), Right(), and
Replace() functions to insert characters into a returned list of my
date field. The date currently looks like this "06202006" the function
I am looking for would return "06/20/2006" something like this if
Insert() was an actual function:
SELECT DISTINCT insert("/", right(insert("/",left(date, 2)), 2)
FROM tblLogs;
Please let me know if you have any experience with this.Perhaps you are looking for STUFF:
SELECT STUFF(STUFF([date], 5, 0, '/'), 3, 0, '/')
FROM tblLogs;
However, data formatting is better done in the presentation layer rather
than the database code.
Hope this helps.
Dan Guzman
SQL Server MVP
"EESP" <johnson4@.wwu.edu> wrote in message
news:1150932662.524736.128910@.b68g2000cwa.googlegroups.com...
> Hi everyone!
> I am looking for a function similar to the Left(), Right(), and
> Replace() functions to insert characters into a returned list of my
> date field. The date currently looks like this "06202006" the function
> I am looking for would return "06/20/2006" something like this if
> Insert() was an actual function:
> SELECT DISTINCT insert("/", right(insert("/",left(date, 2)), 2)
> FROM tblLogs;
> Please let me know if you have any experience with this.
>|||Works Great! I would like to format in the presentation layer of the
code, but I'm having troble running that formatting code through a
datgrid returning close to 10K entries. For some reason just works a
lot faster server side. Thanks for the help!

Formating of columns in output of a SQL Statement in Query Analyzer

Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
-------- --------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---- ------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdjand the difference is?|||What is the difference between two outputs?|||above the example is not appearing like I want but I want to delimite the size of a column setting the tool.|||Originally posted by joelperez
Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
-------- --------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---- ------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

left(col1,10) ?|||Originally posted by snail
left(col1,10) ?

yes left function for me!!!

Thanks!!!|||...or use CAST or CONVERT to specify the column widths of the resulting fields.|||Originally posted by blindman
...or use CAST or CONVERT to specify the column widths of the resulting fields.

Thanks for your recommendation blindman

formating in SqlServer vs Reporting Services

All else being equal, is it faster to use functions like ROUND and TRIM
in the SELECT statement of the query and let the db process it or to
set formating in the layout of the report and let Reporting Services
handle it?
In my case, all is not equal. We have the DB on a very powerful machine
that at sometimes is very overtaxed and runs smoothly at others. RS is
moving to a decently powerful server that it will have exclusively.
Thanks
Louis Ryder
SSRS Report DeveloperMy two cents. Formatting is a presentation concern. Rounding to 2 decimals
can be handled in the presentation layer (i.e., RS) using format strings.
TRIM isn't necessary unless you have some smokin' character columns...
-Tim
"Louis Ryder" <lroskind@.ryderauto.com> wrote in message
news:1152203807.627376.268390@.p79g2000cwp.googlegroups.com...
> All else being equal, is it faster to use functions like ROUND and TRIM
> in the SELECT statement of the query and let the db process it or to
> set formating in the layout of the report and let Reporting Services
> handle it?
> In my case, all is not equal. We have the DB on a very powerful machine
> that at sometimes is very overtaxed and runs smoothly at others. RS is
> moving to a decently powerful server that it will have exclusively.
> Thanks
> Louis Ryder
> SSRS Report Developer
>

Format text box with parentheses if negative

The users want to see negative values like this:

(-$1,000)

How do I format negative values that way without some major iif statement?

Hello,

I don't think you can unless you use an IIf statement.

=IIf(Fields!Amount.Value < 0, "(" & Fields!Amount.Value & ")", Fields!Amount.Value)

Hope this helps.

Jarret

|||

Haven't tested this formula I think it is similar to what Excel uses.

$#,##0_);(-$#,##0)

or

x,xx0.00_;(x,xx0.00(_)

cheers,

Andrew

|||

I have tested this one though...

#0.00;(#0.00)

cheers,

Andrew

|||

Try to use

FormatNumber(field!Num.Value, 0, 0, -1, 0)

This might work for you.

-Rohit

|||

Well, I've learned my something new for today. I'm guessing when you use a semicolon, the left side is the format for positive numbers and the right is for negative numbers? Thanks Andrew!

This one worked for me.

$#,##0.##;(-$#,##0.##)

Jarret

Format Negative Number

Does anyone know how I can use the format statement to show -234.00 as
(234.00)?Jeannie wrote:
> Does anyone know how I can use the format statement to show -234.00 as
> (234.00)?
Never mind - I didn't realize that a simple "C" would do it!