Passing LoginName into SQL query

How do I pass the user's LoginName into the WHERE clause of a SQL query?

I created the following query, but I don't know how to get the user's LoginName and pass its value into Param1. Nothing I try works.

SELECT property.propertyid, property.shortdesc, property.shortdesclink, property.text, property.UserID, [User].UserID AS Expr1, [User].Name FROM property INNER JOIN [User] ON property.UserID = [User].UserID WHERE ([User].Name = @.Param1)

hi, when ever u connecting with sqlserver then

following code will help u in passing parameter

Dim sqlcmd as new sqlcommand

sqlcmd.commandtext =" your query"

Sqlcmd.commandtype= commandtype.text

sqlcmd.parameters.add("@.param1", "loginName")

just add the above lines i hope it will help u.

Passing in a Parameter

Hi All,
I have a Stored Proc and would like to pass in a VARCHAR parameter. The
problem is that the query will need to use it in a IN clause...
DECLARE @.PARAM AS VARCHAR(20)
SET @.PARAM = 'Dan', 'Mike', 'Lisa'
SELECT *
FROM Orders
WHERE Name IN (@.PARAM)
is there a away around this?
Thanks,
Kunkel
On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:

>Hi All,
>I have a Stored Proc and would like to pass in a VARCHAR parameter. The
>problem is that the query will need to use it in a IN clause...
>DECLARE @.PARAM AS VARCHAR(20)
>SET @.PARAM = 'Dan', 'Mike', 'Lisa'
>SELECT *
>FROM Orders
>WHERE Name IN (@.PARAM)
>is there a away around this?
Hi Kunkel,
Several. And they are all described and compared at
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||thanks for that link! it was very helpful. but after i implemented the
articles ideas, i came across this solution:
DECLARE @.Var AS VARCHAR(100)
SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
DECLARE @.SQL AS varchar(1000)
SET @.SQL = 'SELECT *
FROM ORDERS
WHERE
NAME IN (' + @.Var + ')'
Exec(@.SQL)
"Hugo Kornelis" wrote:

> On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:
>
> Hi Kunkel,
> Several. And they are all described and compared at
> http://www.sommarskog.se/arrays-in-sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 28 Apr 2005 15:30:02 -0700, Kunkel wrote:

>thanks for that link! it was very helpful. but after i implemented the
>articles ideas, i came across this solution:
>DECLARE @.Var AS VARCHAR(100)
>SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
>DECLARE @.SQL AS varchar(1000)
>SET @.SQL = 'SELECT *
>FROM ORDERS
>WHERE
>NAME IN (' + @.Var + ')'
>Exec(@.SQL)
Hi Kunkel,
This is dynamic SQL, and I believe that this techinique is discussed at
Erland's site as well. Please don't do this if you can avoid it. It is a
severe breach of security - you give malicious users the ability to
inject SQL.
Erland has a great article with an in-depth explanation of pros and cons
of dynamic SQL as well: http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Passing Date Parameter to Oracle Query

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.

But when I run the report from Preview, I get no results at all no matter what format I use in the function.

Any chance any of you have seen this and know how to work with it?

The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||

Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.

I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?

|||Hi,

Did you ever find a solution to this problem?

Cheers

Phil|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||

Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

|||

Use this syntax to omit time portion:

WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

sql

Passing Date Parameter to Oracle Query

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.

But when I run the report from Preview, I get no results at all no matter what format I use in the function.

Any chance any of you have seen this and know how to work with it?

The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||

Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.

I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?

|||Hi,

Did you ever find a solution to this problem?

Cheers

Phil|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||

Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

|||

Use this syntax to omit time portion:

WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

Passing Date Parameter to Oracle Query

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.

But when I run the report from Preview, I get no results at all no matter what format I use in the function.

Any chance any of you have seen this and know how to work with it?

The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||

Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.

I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?

|||Hi,

Did you ever find a solution to this problem?

Cheers

Phil
|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||

Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

|||

Use this syntax to omit time portion:

WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

Passing Date Parameter to Oracle Query

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.

But when I run the report from Preview, I get no results at all no matter what format I use in the function.

Any chance any of you have seen this and know how to work with it?

The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||

Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.

I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?

|||Hi,

Did you ever find a solution to this problem?

Cheers

Phil
|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||

Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

|||

Use this syntax to omit time portion:

WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

Passing a WHERE clause

I want to build a SQL WHERE (and possibly ORDER BY) clause
in a c# application and then pass them as parameters to a report that would
be used by a SQL Server stored procedure.
can anyone tell me how to do that?
Syntax for building it?
maybe a link to an example?
thanks
--
Lucas DargisYou have a very good sample available in samples. search for forms
authentication and open the custom security c# file and see the code you will
get good idea about how to write.
Amarnath
"akbikerboy" wrote:
> I want to build a SQL WHERE (and possibly ORDER BY) clause
> in a c# application and then pass them as parameters to a report that would
> be used by a SQL Server stored procedure.
> can anyone tell me how to do that?
> Syntax for building it?
> maybe a link to an example?
> thanks
> --
> Lucas Dargis|||could you be more specific?
Are you talking about MSDN or in VS?
I can't find what you are talking about
thanks
--
Lucas Dargis
"Amarnath" wrote:
> You have a very good sample available in samples. search for forms
> authentication and open the custom security c# file and see the code you will
> get good idea about how to write.
> Amarnath
> "akbikerboy" wrote:
> > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > in a c# application and then pass them as parameters to a report that would
> > be used by a SQL Server stored procedure.
> >
> > can anyone tell me how to do that?
> > Syntax for building it?
> > maybe a link to an example?
> >
> > thanks
> > --
> > Lucas Dargis|||sql server online help samples
Amarnath
"akbikerboy" wrote:
> could you be more specific?
> Are you talking about MSDN or in VS?
> I can't find what you are talking about
>
> thanks
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > You have a very good sample available in samples. search for forms
> > authentication and open the custom security c# file and see the code you will
> > get good idea about how to write.
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > in a c# application and then pass them as parameters to a report that would
> > > be used by a SQL Server stored procedure.
> > >
> > > can anyone tell me how to do that?
> > > Syntax for building it?
> > > maybe a link to an example?
> > >
> > > thanks
> > > --
> > > Lucas Dargis|||I looked all over and i can't find what your are talking about. do you have a
link to it?
--
Lucas Dargis
"Amarnath" wrote:
> sql server online help samples
> Amarnath
> "akbikerboy" wrote:
> > could you be more specific?
> > Are you talking about MSDN or in VS?
> > I can't find what you are talking about
> >
> >
> > thanks
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > You have a very good sample available in samples. search for forms
> > > authentication and open the custom security c# file and see the code you will
> > > get good idea about how to write.
> > >
> > > Amarnath
> > >
> > > "akbikerboy" wrote:
> > >
> > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > in a c# application and then pass them as parameters to a report that would
> > > > be used by a SQL Server stored procedure.
> > > >
> > > > can anyone tell me how to do that?
> > > > Syntax for building it?
> > > > maybe a link to an example?
> > > >
> > > > thanks
> > > > --
> > > > Lucas Dargis|||Just go to search in online help and search for forms authentication and if
you have installed the samples, go to the samples folder and see the forms
authentication example code.
Amarnath
"akbikerboy" wrote:
> I looked all over and i can't find what your are talking about. do you have a
> link to it?
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > sql server online help samples
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > could you be more specific?
> > > Are you talking about MSDN or in VS?
> > > I can't find what you are talking about
> > >
> > >
> > > thanks
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > You have a very good sample available in samples. search for forms
> > > > authentication and open the custom security c# file and see the code you will
> > > > get good idea about how to write.
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > in a c# application and then pass them as parameters to a report that would
> > > > > be used by a SQL Server stored procedure.
> > > > >
> > > > > can anyone tell me how to do that?
> > > > > Syntax for building it?
> > > > > maybe a link to an example?
> > > > >
> > > > > thanks
> > > > > --
> > > > > Lucas Dargis|||there are a million articles about forms authentication. and i don't see what
that has to do with passing a where clause from an application to RS...
thanks for trying though.
--
Lucas Dargis
"Amarnath" wrote:
> Just go to search in online help and search for forms authentication and if
> you have installed the samples, go to the samples folder and see the forms
> authentication example code.
> Amarnath
> "akbikerboy" wrote:
> > I looked all over and i can't find what your are talking about. do you have a
> > link to it?
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > sql server online help samples
> > >
> > > Amarnath
> > >
> > > "akbikerboy" wrote:
> > >
> > > > could you be more specific?
> > > > Are you talking about MSDN or in VS?
> > > > I can't find what you are talking about
> > > >
> > > >
> > > > thanks
> > > > --
> > > > Lucas Dargis
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > You have a very good sample available in samples. search for forms
> > > > > authentication and open the custom security c# file and see the code you will
> > > > > get good idea about how to write.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "akbikerboy" wrote:
> > > > >
> > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > be used by a SQL Server stored procedure.
> > > > > >
> > > > > > can anyone tell me how to do that?
> > > > > > Syntax for building it?
> > > > > > maybe a link to an example?
> > > > > >
> > > > > > thanks
> > > > > > --
> > > > > > Lucas Dargis|||dude, I didn't ask you to type it on google to get million option. By the way
I was mentioning that if you have sample installed, you can have a look at
the sample code, I repeat the code, so that you will get some idea for
writing code in c# or VB.net.
Amarnath
"akbikerboy" wrote:
> there are a million articles about forms authentication. and i don't see what
> that has to do with passing a where clause from an application to RS...
> thanks for trying though.
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > Just go to search in online help and search for forms authentication and if
> > you have installed the samples, go to the samples folder and see the forms
> > authentication example code.
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > I looked all over and i can't find what your are talking about. do you have a
> > > link to it?
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > sql server online help samples
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > could you be more specific?
> > > > > Are you talking about MSDN or in VS?
> > > > > I can't find what you are talking about
> > > > >
> > > > >
> > > > > thanks
> > > > > --
> > > > > Lucas Dargis
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > You have a very good sample available in samples. search for forms
> > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > get good idea about how to write.
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "akbikerboy" wrote:
> > > > > >
> > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > be used by a SQL Server stored procedure.
> > > > > > >
> > > > > > > can anyone tell me how to do that?
> > > > > > > Syntax for building it?
> > > > > > > maybe a link to an example?
> > > > > > >
> > > > > > > thanks
> > > > > > > --
> > > > > > > Lucas Dargis|||Ok, to guide you precisly,
If you have samples installed in your m/c, go to
C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
accordingly)
you can see a custom security solution file, click on that and open. search
for
"AuthenticationExtension.cs" file and click on view code and search for
"VerifyUser"
In that code you can see how it is defined for stored procedure using
commandtype
and parameter.add is used. instead of "@.username" build your where clause by
defining a variable and start adding all the conditions and pass it on using
parameter.add.
I think this should give some idea about how to write it.
Please let me know if you have any problems
Amarnath
"akbikerboy" wrote:
> there are a million articles about forms authentication. and i don't see what
> that has to do with passing a where clause from an application to RS...
> thanks for trying though.
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > Just go to search in online help and search for forms authentication and if
> > you have installed the samples, go to the samples folder and see the forms
> > authentication example code.
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > I looked all over and i can't find what your are talking about. do you have a
> > > link to it?
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > sql server online help samples
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > could you be more specific?
> > > > > Are you talking about MSDN or in VS?
> > > > > I can't find what you are talking about
> > > > >
> > > > >
> > > > > thanks
> > > > > --
> > > > > Lucas Dargis
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > You have a very good sample available in samples. search for forms
> > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > get good idea about how to write.
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "akbikerboy" wrote:
> > > > > >
> > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > be used by a SQL Server stored procedure.
> > > > > > >
> > > > > > > can anyone tell me how to do that?
> > > > > > > Syntax for building it?
> > > > > > > maybe a link to an example?
> > > > > > >
> > > > > > > thanks
> > > > > > > --
> > > > > > > Lucas Dargis|||Well thanks for pointing that out precisely.
I guess I should have been more specific. I know how to create and pass a
parameter. Thatâ's not what I was asking. My question was how to write and
pass a 'where' clause specifically. What syntax to use.
For example: "WHERE EventDate = â'01/01/2007â' AND LocationCode = 'LA' AND zip
= 99874"
In that string, I have slashes, and quotes. When I try to pass this string
as a parameter, I get errors. Iâ've tried every type of â'escapeâ' technique
that I know and nothing works.
Thanks again.
--
Lucas Dargis
"Amarnath" wrote:
> Ok, to guide you precisly,
> If you have samples installed in your m/c, go to
> C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
> Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
> accordingly)
> you can see a custom security solution file, click on that and open. search
> for
> "AuthenticationExtension.cs" file and click on view code and search for
> "VerifyUser"
> In that code you can see how it is defined for stored procedure using
> commandtype
> and parameter.add is used. instead of "@.username" build your where clause by
> defining a variable and start adding all the conditions and pass it on using
> parameter.add.
> I think this should give some idea about how to write it.
> Please let me know if you have any problems
> Amarnath
>
> "akbikerboy" wrote:
> > there are a million articles about forms authentication. and i don't see what
> > that has to do with passing a where clause from an application to RS...
> >
> > thanks for trying though.
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > Just go to search in online help and search for forms authentication and if
> > > you have installed the samples, go to the samples folder and see the forms
> > > authentication example code.
> > >
> > > Amarnath
> > >
> > > "akbikerboy" wrote:
> > >
> > > > I looked all over and i can't find what your are talking about. do you have a
> > > > link to it?
> > > > --
> > > > Lucas Dargis
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > sql server online help samples
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "akbikerboy" wrote:
> > > > >
> > > > > > could you be more specific?
> > > > > > Are you talking about MSDN or in VS?
> > > > > > I can't find what you are talking about
> > > > > >
> > > > > >
> > > > > > thanks
> > > > > > --
> > > > > > Lucas Dargis
> > > > > >
> > > > > >
> > > > > > "Amarnath" wrote:
> > > > > >
> > > > > > > You have a very good sample available in samples. search for forms
> > > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > > get good idea about how to write.
> > > > > > >
> > > > > > > Amarnath
> > > > > > >
> > > > > > > "akbikerboy" wrote:
> > > > > > >
> > > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > > be used by a SQL Server stored procedure.
> > > > > > > >
> > > > > > > > can anyone tell me how to do that?
> > > > > > > > Syntax for building it?
> > > > > > > > maybe a link to an example?
> > > > > > > >
> > > > > > > > thanks
> > > > > > > > --
> > > > > > > > Lucas Dargis|||Oh Ok, so you meant this. the same way, how you have used in the example,
with double quotes and single quotes. if doesnt work try concatenating some
thing like this. e.g "Where aa = " + " ' " + "01/01/2007" + " ' "
Let me know whether it is working ?
Amarnath
"akbikerboy" wrote:
> Well thanks for pointing that out precisely.
> I guess I should have been more specific. I know how to create and pass a
> parameter. Thatâ's not what I was asking. My question was how to write and
> pass a 'where' clause specifically. What syntax to use.
> For example: "WHERE EventDate = â'01/01/2007â' AND LocationCode = 'LA' AND zip
> = 99874"
> In that string, I have slashes, and quotes. When I try to pass this string
> as a parameter, I get errors. Iâ've tried every type of â'escapeâ' technique
> that I know and nothing works.
> Thanks again.
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > Ok, to guide you precisly,
> >
> > If you have samples installed in your m/c, go to
> >
> > C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
> > Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
> > accordingly)
> >
> > you can see a custom security solution file, click on that and open. search
> > for
> > "AuthenticationExtension.cs" file and click on view code and search for
> > "VerifyUser"
> >
> > In that code you can see how it is defined for stored procedure using
> > commandtype
> > and parameter.add is used. instead of "@.username" build your where clause by
> > defining a variable and start adding all the conditions and pass it on using
> > parameter.add.
> >
> > I think this should give some idea about how to write it.
> > Please let me know if you have any problems
> >
> > Amarnath
> >
> >
> > "akbikerboy" wrote:
> >
> > > there are a million articles about forms authentication. and i don't see what
> > > that has to do with passing a where clause from an application to RS...
> > >
> > > thanks for trying though.
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > Just go to search in online help and search for forms authentication and if
> > > > you have installed the samples, go to the samples folder and see the forms
> > > > authentication example code.
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > I looked all over and i can't find what your are talking about. do you have a
> > > > > link to it?
> > > > > --
> > > > > Lucas Dargis
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > sql server online help samples
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "akbikerboy" wrote:
> > > > > >
> > > > > > > could you be more specific?
> > > > > > > Are you talking about MSDN or in VS?
> > > > > > > I can't find what you are talking about
> > > > > > >
> > > > > > >
> > > > > > > thanks
> > > > > > > --
> > > > > > > Lucas Dargis
> > > > > > >
> > > > > > >
> > > > > > > "Amarnath" wrote:
> > > > > > >
> > > > > > > > You have a very good sample available in samples. search for forms
> > > > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > > > get good idea about how to write.
> > > > > > > >
> > > > > > > > Amarnath
> > > > > > > >
> > > > > > > > "akbikerboy" wrote:
> > > > > > > >
> > > > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > > > be used by a SQL Server stored procedure.
> > > > > > > > >
> > > > > > > > > can anyone tell me how to do that?
> > > > > > > > > Syntax for building it?
> > > > > > > > > maybe a link to an example?
> > > > > > > > >
> > > > > > > > > thanks
> > > > > > > > > --
> > > > > > > > > Lucas Dargis|||so for the query i tried
select....
where @.where
parameter: LocationCode + "'" + LA + "'"
I am getting the following error
An error occurred whild executing the query
An expression of non-boolean type specified in a context where a condition
is expected, near @.where
error:4145
I am running the query in the report designed in the Data tab
thanks
--
Lucas Dargis
"akbikerboy" wrote:
> I want to build a SQL WHERE (and possibly ORDER BY) clause
> in a c# application and then pass them as parameters to a report that would
> be used by a SQL Server stored procedure.
> can anyone tell me how to do that?
> Syntax for building it?
> maybe a link to an example?
> thanks
> --
> Lucas Dargis|||For the query i use
SELECT...
WHERE @.where
parameter: declared as a string
LocationCode = + "'" + LA + "'"
and i got the following error:
An error occurred whild executing the query.
An expression of non-boolean type specified in a contex where a condition is
expected, near '@.where'.
Error: 4145
I am running the query in the Data tab in the Report Builder
thanks
--
Lucas Dargis
"Amarnath" wrote:
> Oh Ok, so you meant this. the same way, how you have used in the example,
> with double quotes and single quotes. if doesnt work try concatenating some
> thing like this. e.g "Where aa = " + " ' " + "01/01/2007" + " ' "
> Let me know whether it is working ?
> Amarnath
> "akbikerboy" wrote:
> > Well thanks for pointing that out precisely.
> >
> > I guess I should have been more specific. I know how to create and pass a
> > parameter. Thatâ's not what I was asking. My question was how to write and
> > pass a 'where' clause specifically. What syntax to use.
> >
> > For example: "WHERE EventDate = â'01/01/2007â' AND LocationCode = 'LA' AND zip
> > = 99874"
> >
> > In that string, I have slashes, and quotes. When I try to pass this string
> > as a parameter, I get errors. Iâ've tried every type of â'escapeâ' technique
> > that I know and nothing works.
> >
> > Thanks again.
> >
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > Ok, to guide you precisly,
> > >
> > > If you have samples installed in your m/c, go to
> > >
> > > C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
> > > Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
> > > accordingly)
> > >
> > > you can see a custom security solution file, click on that and open. search
> > > for
> > > "AuthenticationExtension.cs" file and click on view code and search for
> > > "VerifyUser"
> > >
> > > In that code you can see how it is defined for stored procedure using
> > > commandtype
> > > and parameter.add is used. instead of "@.username" build your where clause by
> > > defining a variable and start adding all the conditions and pass it on using
> > > parameter.add.
> > >
> > > I think this should give some idea about how to write it.
> > > Please let me know if you have any problems
> > >
> > > Amarnath
> > >
> > >
> > > "akbikerboy" wrote:
> > >
> > > > there are a million articles about forms authentication. and i don't see what
> > > > that has to do with passing a where clause from an application to RS...
> > > >
> > > > thanks for trying though.
> > > > --
> > > > Lucas Dargis
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Just go to search in online help and search for forms authentication and if
> > > > > you have installed the samples, go to the samples folder and see the forms
> > > > > authentication example code.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "akbikerboy" wrote:
> > > > >
> > > > > > I looked all over and i can't find what your are talking about. do you have a
> > > > > > link to it?
> > > > > > --
> > > > > > Lucas Dargis
> > > > > >
> > > > > >
> > > > > > "Amarnath" wrote:
> > > > > >
> > > > > > > sql server online help samples
> > > > > > >
> > > > > > > Amarnath
> > > > > > >
> > > > > > > "akbikerboy" wrote:
> > > > > > >
> > > > > > > > could you be more specific?
> > > > > > > > Are you talking about MSDN or in VS?
> > > > > > > > I can't find what you are talking about
> > > > > > > >
> > > > > > > >
> > > > > > > > thanks
> > > > > > > > --
> > > > > > > > Lucas Dargis
> > > > > > > >
> > > > > > > >
> > > > > > > > "Amarnath" wrote:
> > > > > > > >
> > > > > > > > > You have a very good sample available in samples. search for forms
> > > > > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > > > > get good idea about how to write.
> > > > > > > > >
> > > > > > > > > Amarnath
> > > > > > > > >
> > > > > > > > > "akbikerboy" wrote:
> > > > > > > > >
> > > > > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > > > > be used by a SQL Server stored procedure.
> > > > > > > > > >
> > > > > > > > > > can anyone tell me how to do that?
> > > > > > > > > > Syntax for building it?
> > > > > > > > > > maybe a link to an example?
> > > > > > > > > >
> > > > > > > > > > thanks
> > > > > > > > > > --
> > > > > > > > > > Lucas Dargis|||By two way you can solve this.
1. use stored proc to concatenate all the "select", includng the values
which comes from parameter ie where clause
2. By directly giving in the data tab..
e.g I have taken some samples from adventure... it goes like this..
paste this code in the data tab it executes successfully..
declare @.s as nvarchar(1000)
set @.s = 'SELECT ContactID, NameStyle, Title, FirstName, FROM
Person.Contact WHERE ' + @.w
exec sp_executesql @.s
the @.w is what I entered " as (Title = 'Mr.').
PS. "where", is the reserved word, so name @.where to some other name.
So basically what i meant here is to built using stored proc or use string
and concatenate with your parameter.
Amarnath
"akbikerboy" wrote:
> so for the query i tried
> select....
> where @.where
> parameter: LocationCode + "'" + LA + "'"
> I am getting the following error
> An error occurred whild executing the query
> An expression of non-boolean type specified in a context where a condition
> is expected, near @.where
> error:4145
> I am running the query in the report designed in the Data tab
> thanks
> --
> Lucas Dargis
>
> "akbikerboy" wrote:
> > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > in a c# application and then pass them as parameters to a report that would
> > be used by a SQL Server stored procedure.
> >
> > can anyone tell me how to do that?
> > Syntax for building it?
> > maybe a link to an example?
> >
> > thanks
> > --
> > Lucas Dargis|||AWESOME! very clever.
well your second solution is just what i was looking for, however, it brings
up another problem.
Since the query is now created in a variable, RS doesn't recognize the
fields in the layout tab. how do i reference the fields so i can create the
report?
thanks again
--
Lucas Dargis
"Amarnath" wrote:
> By two way you can solve this.
> 1. use stored proc to concatenate all the "select", includng the values
> which comes from parameter ie where clause
> 2. By directly giving in the data tab..
> e.g I have taken some samples from adventure... it goes like this..
> paste this code in the data tab it executes successfully..
> declare @.s as nvarchar(1000)
> set @.s = 'SELECT ContactID, NameStyle, Title, FirstName, FROM
> Person.Contact WHERE ' + @.w
> exec sp_executesql @.s
> the @.w is what I entered " as (Title = 'Mr.').
> PS. "where", is the reserved word, so name @.where to some other name.
> So basically what i meant here is to built using stored proc or use string
> and concatenate with your parameter.
> Amarnath
>
> "akbikerboy" wrote:
> > so for the query i tried
> > select....
> > where @.where
> >
> > parameter: LocationCode + "'" + LA + "'"
> >
> > I am getting the following error
> >
> > An error occurred whild executing the query
> > An expression of non-boolean type specified in a context where a condition
> > is expected, near @.where
> >
> > error:4145
> >
> > I am running the query in the report designed in the Data tab
> >
> > thanks
> > --
> > Lucas Dargis
> >
> >
> > "akbikerboy" wrote:
> >
> > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > in a c# application and then pass them as parameters to a report that would
> > > be used by a SQL Server stored procedure.
> > >
> > > can anyone tell me how to do that?
> > > Syntax for building it?
> > > maybe a link to an example?
> > >
> > > thanks
> > > --
> > > Lucas Dargis|||So you can forget my last question. I reread your last post and figured it out.
i put the sql code you provided me into a stored proc and called that proc
while sending the Where Clause as a parameter and it worked PERFECTLY!!!
i can't thank you enough. i've been stuck on this for a few weeks.
Lucas Dargis
"akbikerboy" wrote:
> AWESOME! very clever.
> well your second solution is just what i was looking for, however, it brings
> up another problem.
> Since the query is now created in a variable, RS doesn't recognize the
> fields in the layout tab. how do i reference the fields so i can create the
> report?
> thanks again
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > By two way you can solve this.
> > 1. use stored proc to concatenate all the "select", includng the values
> > which comes from parameter ie where clause
> > 2. By directly giving in the data tab..
> > e.g I have taken some samples from adventure... it goes like this..
> >
> > paste this code in the data tab it executes successfully..
> >
> > declare @.s as nvarchar(1000)
> > set @.s = 'SELECT ContactID, NameStyle, Title, FirstName, FROM
> > Person.Contact WHERE ' + @.w
> > exec sp_executesql @.s
> >
> > the @.w is what I entered " as (Title = 'Mr.').
> >
> > PS. "where", is the reserved word, so name @.where to some other name.
> > So basically what i meant here is to built using stored proc or use string
> > and concatenate with your parameter.
> >
> > Amarnath
> >
> >
> > "akbikerboy" wrote:
> >
> > > so for the query i tried
> > > select....
> > > where @.where
> > >
> > > parameter: LocationCode + "'" + LA + "'"
> > >
> > > I am getting the following error
> > >
> > > An error occurred whild executing the query
> > > An expression of non-boolean type specified in a context where a condition
> > > is expected, near @.where
> > >
> > > error:4145
> > >
> > > I am running the query in the report designed in the Data tab
> > >
> > > thanks
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "akbikerboy" wrote:
> > >
> > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > in a c# application and then pass them as parameters to a report that would
> > > > be used by a SQL Server stored procedure.
> > > >
> > > > can anyone tell me how to do that?
> > > > Syntax for building it?
> > > > maybe a link to an example?
> > > >
> > > > thanks
> > > > --
> > > > Lucas Dargis

Passing a string for IN clause

Hello,

How do I pass a string parameter to a Stored Procedure with an 'in' clause?

ex.
Select * FROM Persons
WHERE PersonID IN (1,2,3,4,5,20,56,80)

How do I define my Store Procedure so I can pass the values between () as a string (nvarchar) parameter to the SqlCommand?

Thanks,
WYou would have to use dynamic SQL (create a string witht he values to create a SQL command) or pass in a delimited string, and then create a function to split the values and return a table, and use that returned table object in the IN clause.

Passing a stored procedure parameter into an IN clause

Hi All :)

I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @.CustCode). The parameter is passed using a DECommand object in VB6.

I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@.CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@.CustCode1, @.CustCode2,...etc), but do not want to limit the number of customers.

If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.

I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:

KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)

and

'KA1001', 'KA1002'

Both fail :(

Any ideas?

Regards

Xoyou need to parse your list into a table variable and then join to that table variable.|||How about

WHERE
','+@.CustCode+','
LIKE
'%,'+CustomerCode+',%'

perhaps?

It's crap (won't use an index) but does work and is easy (might need some trims in there).
These requirements are really dodgy but sadly very common. You shoudl really try using an array of some kind. I would stay away from dynamic SQL which is the other way.|||You can also do this by passing the list of parameters as nText and using XML to add as many options you want. In the sproc you will need to use sp_xml_PrepareDocument to force that into a local temp table.

It's all kinda tricky, but crafty at the same time.|||LoztInSpace: Don't quite understand the syntax! Can you post an example! Thanks

alex8675: Thanks but no thanks!!! :)

Thrasymachus: Thanks!! Have used your solution for now :)|||Bookmark this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string|||LoztInSpace: Don't quite understand the syntax! Can you post an example! Thanks

What's to not understand? It's just a where statement. Did you try it?|||Hi Xogon

A bit of a bumpy ride for your second post methinks - don't take it personal, sometimes people forget what it's like to not fully understand the basics.

One easy Option for you - but not the most efficient is to build you SQL Statement in a VarChar and then EXECUTE it.

Heres an example


Declare @.SQL VarChar(1000)

SELECT @.SQL = ' SELECT [something]'
SELECT @.SQL = @.SQL + ' FROM [Table]'
SELECT @.SQL = @.SQL + ' WHERE CustomerCode IN (' + @.CustCode + ')'

EXECUTE (@.SQL)

And watch out for the Single Quotes in your @.CustCode these will have to be doubled up

GW|||ummmmmmmmmmm...did you read the link I posted?|||thanks GWilliy - worked a treat!! :)|||UUummmmmmmmmmmmmmmmmm - LOL

I Obviously did Brett & found the link in the Thread to another thread which had this Code.CREATE Procedure sp_dynamic_test
@.TableName varChar(100),
@.ID1 varchar(100),
@.ID2 varchar(100),
@.ID3 varchar(100),
@.DX varchar(100),
@.family varchar(100)
AS

Declare @.SQL VarChar(1000)

SELECT @.SQL = ' SELECT FIRSTNAME, LASTNAME, @.ID1, @.ID2, @.ID3, @.DX '
SELECT @.SQL = @.SQL + ' FROM '
SELECT @.SQL = @.SQL + @.TableName
SELECT @.SQL = @.SQL + ' WHERE Family = COALESCE((NULLIF(@.Family,0)),Family) '

Exec ( @.SQL)

Probably Naughty of me to do the Leg work for xogon, Plagarize & Dumb the code down.

I don't know what Skill level xogon is @. (2nd Post) but I thought a Concise & Clear example is often a good place to start. Complexities & doing his own Googles will organically follow.

DBForums is a Fantastic Site but I think sometimes posting a link to a related thread can frustrate New members, Although I will accept a point about too much hand holding.

hopefuly I've not upset anyone

GW|||Still, the use of the table variable is MUCH preferred over building dynamic SQL like you are suggesting and as I fear xogon has implemented.

We actually have built a function that takes as input the CSV string of (in this case, customer numbers), parses it, and returns the required table. That way I offer some reasonable methodology to the unwashed masses of developers here that need to do what you are doing in some form or fashion.

I didn't check Brett's link, but I think this function was derived from an earlier query I made on this very subject eons ago.CREATE FUNCTION [dbo].[fn_CSVList_FilteredPortfolioTable] (@.CsvList varchar(4000))
RETURNS table
AS

RETURN ( SELECT TOP 100 PERCENT *
FROM dbo.Portfolio (nolock)
WHERE ((CHARINDEX(',' + CAST(PortfolioID AS VARCHAR) + ',', ',' + @.CsvList + ',') > 0)
AND (isInactive <> 1))
ORDER BY PortfolioID)this code is probably even more complex than yours would need to be (you really would only need the CHARINDEX line), as it "verifies" the entries in the input CSV string against a table of valid codes.

Still, the use of a function lets you do the conversion on the fly in selects by join, such as: SELECT DISTINCT CL.PortfolioID, CL.StockOSID, @.CreateDate AS CreateDate, BuyDate, SellDate, CL.StockSymbol, Weight
FROM dbo.CurrentList CL (nolock)
INNER JOIN dbo.fn_CSVList_FilteredPortfolioTable(@.PortfoliosT oProcess) FP ON
CL.PortfolioID = FP.PortfolioID
WHERE ((@.CreateDate > CL.Buydate) AND
(@.CreateDate <= CL.SellDate)) AND
(CL.Active = 1)

errrr...and forgive the use of the fn_ prefix on the function name...it was created before I knew better, and at the suggestion of the senior SQL Server developer here at the time. *blush*

Pass XML between SPROCs possible in SQL 2000? 2005?

A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.Hello Dave,

> Now I want to execute my stored procedure using the XML result of part
> A as the parameter for the sproc in part B. Is that possible? The
> only way I know how to do it right now is to return the result of A
> using ADO.NET, insert a root element (to make the XML valid), and then
> call the sproc created in part B with this xml as the parameter.
In 2005, you'd pass the XML as string to a SQLCLR stored proc. That stored
proc could parse the XML using a XmlReader, extact the command text and para
meter
values and have that proc execute other ones.
This is like the second time today that is idea has come up. Seems like it
time for some demo code... ;)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello me, meet the real me,

> This is like the second time today that is idea has come up. Seems
> like it time for some demo code... ;)
Okay, here's the CLR-less version:
http://www.sqljunkies.com/WebLog/kt...er.aspx

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Pass where clause to Report dataset

Hi there,
Does anyone know how to or if it is possible to pass a where clause as a parameter to a dataset for a Reporting Services report? I have a case where depending on what is selected the where clause needs to be based on a different fields. Or otherwise if an if statement can be used in the dataset to overcome this problem.
ThanksI used to do this at design time using conditional expression like this:
="SELECT * FROM myTable" & IIF(Parameters!myValue.value="All", ""," where myColumn =" & Parameters!Linea.value)
or using stored procedures. Not sure you can set dataset definition at run time.
Antonio
"PLSH" wrote:
> Hi there,
> Does anyone know how to or if it is possible to pass a where clause as a parameter to a dataset for a Reporting Services report? I have a case where depending on what is selected the where clause needs to be based on a different fields. Or otherwise if an if statement can be used in the dataset to overcome this problem.
> Thanks|||In the data pane in report designer.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Jolliffe" <MikeJolliffe@.discussions.microsoft.com> wrote in message
news:34FC573B-36E7-44CC-BEEE-A725AD30BBDE@.microsoft.com...
> Antonio
> hi - i'm interested where you put this conditional statement to make it
work
> - in the source of a control or in the data area somewhere? I'd tried
> something similar and could not make it work.
> thanks
> Mike
>
> "Antonio Rome" wrote:
> > I used to do this at design time using conditional expression like this:
> >
> > ="SELECT * FROM myTable" & IIF(Parameters!myValue.value="All", "","
where myColumn =" & Parameters!Linea.value)
> >
> > or using stored procedures. Not sure you can set dataset definition at
run time.
> >
> > Antonio
> >
> > "PLSH" wrote:
> >
> > > Hi there,
> > >
> > > Does anyone know how to or if it is possible to pass a where clause as
a parameter to a dataset for a Reporting Services report? I have a case
where depending on what is selected the where clause needs to be based on a
different fields. Or otherwise if an if statement can be used in the dataset
to overcome this problem.
> > >
> > > Thanks
>|||Do you mean the SQL Pane? Tried this and received syntax errors.
"Ravi Mumulla (Microsoft)" wrote:
> In the data pane in report designer.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Mike Jolliffe" <MikeJolliffe@.discussions.microsoft.com> wrote in message
> news:34FC573B-36E7-44CC-BEEE-A725AD30BBDE@.microsoft.com...
> > Antonio
> > hi - i'm interested where you put this conditional statement to make it
> work
> > - in the source of a control or in the data area somewhere? I'd tried
> > something similar and could not make it work.
> >
> > thanks
> > Mike
> >
> >
> > "Antonio Rome" wrote:
> >
> > > I used to do this at design time using conditional expression like this:
> > >
> > > ="SELECT * FROM myTable" & IIF(Parameters!myValue.value="All", "","
> where myColumn =" & Parameters!Linea.value)
> > >
> > > or using stored procedures. Not sure you can set dataset definition at
> run time.
> > >
> > > Antonio
> > >
> > > "PLSH" wrote:
> > >
> > > > Hi there,
> > > >
> > > > Does anyone know how to or if it is possible to pass a where clause as
> a parameter to a dataset for a Reporting Services report? I have a case
> where depending on what is selected the where clause needs to be based on a
> different fields. Or otherwise if an if statement can be used in the dataset
> to overcome this problem.
> > > >
> > > > Thanks
> >
>
>