Partitions and FileGroups: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.
Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).
|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

sql

Passing Paramenter to SP for Column Name

I need to select status values form 1 of 4 possible columns, and I need to
pass the column name to select on as a parameter to the stored procedure.
Does anyone have an example of the syntax for the stored procedure?

such as:

CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@.SelectColumn nVarChar
)
AS

SET NOCOUNT ON;

SELECT *
FROM Documents
WHERE (@.SelectColumn = 0)

The columns to select on are BIT columns.

The error message on the above SP is:

'Syntax error converting the nvarchar value 'P' to a column of data type
int.'

At this point, the passed in parameter is a string "ProducerStatus"

Thanks
Michaelhi Michael,
You need to use the Dynamic Sql to change the column name at
the run time.

create procedure dbo.sp_Document_select_bystatus
(@.selectColumn varchar(255))
as
set nocount on
declare @.dynamicSql varchar(8000)

select @.dynamicSql = '
SELECT *
FROM Documents
WHERE ( ' + @.selectColumn + ' = 0)
'
execute (@.dynamicSql)
set nocount off
Go

Thank you
santhosh
Michael Jackson wrote:
> I need to select status values form 1 of 4 possible columns, and I
need to
> pass the column name to select on as a parameter to the stored
procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS
> SET NOCOUNT ON;
> SELECT *
> FROM Documents
> WHERE (@.SelectColumn = 0)
> The columns to select on are BIT columns.
> The error message on the above SP is:
> 'Syntax error converting the nvarchar value 'P' to a column of data
type
> int.'
> At this point, the passed in parameter is a string "ProducerStatus"
> Thanks
> Michael|||Thanks for the help. It worked great.

"SSK" <suthramsk@.yahoo.com> wrote in message
news:1107491299.712183.231340@.z14g2000cwz.googlegr oups.com...
> hi Michael,
> You need to use the Dynamic Sql to change the column name at
> the run time.
> create procedure dbo.sp_Document_select_bystatus
> (@.selectColumn varchar(255))
> as
> set nocount on
> declare @.dynamicSql varchar(8000)
> select @.dynamicSql = '
> SELECT *
> FROM Documents
> WHERE ( ' + @.selectColumn + ' = 0)
> '
> execute (@.dynamicSql)
> set nocount off
> Go
> Thank you
> santhosh
> Michael Jackson wrote:
>> I need to select status values form 1 of 4 possible columns, and I
> need to
>> pass the column name to select on as a parameter to the stored
> procedure.
>> Does anyone have an example of the syntax for the stored procedure?
>>
>> such as:
>>
>> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
>> (
>> @.SelectColumn nVarChar
>> )
>> AS
>>
>> SET NOCOUNT ON;
>>
>> SELECT *
>> FROM Documents
>> WHERE (@.SelectColumn = 0)
>>
>> The columns to select on are BIT columns.
>>
>> The error message on the above SP is:
>>
>> 'Syntax error converting the nvarchar value 'P' to a column of data
> type
>> int.'
>>
>> At this point, the passed in parameter is a string "ProducerStatus"
>>
>> Thanks
>> Michael|||Avoid dynamic SQL if you can. In this case you don't need it:

SELECT col1
FROM Documents
WHERE col1 = 0 AND @.selectcolumn = 'col1'
UNION ALL
SELECT col2
FROM Documents
WHERE col2 = 0 AND @.selectcolumn = 'col2'
UNION ALL
SELECT col3
FROM Documents
WHERE col3 = 0 AND @.selectcolumn = 'col3'
UNION ALL
SELECT col4
FROM Documents
WHERE col4 = 0 AND @.selectcolumn = 'col4'

To understand why dynamic SQL isn't a good idea for this, see:

http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--|||Michael Jackson (stratojack@.cox.net) writes:
> I need to select status values form 1 of 4 possible columns, and I need to
> pass the column name to select on as a parameter to the stored procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS

To add to the other responses, permit me to point out two other flaws:

1) sp_ is a prefix that is reserved for system procedures, and SQL Server
will first look for these in master. Don't use it for your own code.

2) nvarchar without lengthspeciication is the same as nvarchar(1), hardly
what you want.

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

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

Passing OLAP Parameters Within a URL

Hi there!

My report uses an OLAP query with a (multi-valued) parameter, the MDX generated query is something like "...ON ROWS FROM ( SELECT ( STRTOSET(@.Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".

The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:

https://servername/ReportServer?/PathTo/MyReport&rs:Command=Render&rs:Format=HTML4.0&Region=[foo].[bar 123].&[baz]

But I cannot get Reporting Services to accept the parameter instead of complaining about a missing parameter value. I've already tried quoting the braces, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.

How do I quote this parameter correctly?

I do this befor but so it was as follow

Report server URL(http://Machinename/reportserver/) + Reports Folder (MyReports/)+Reportname (My report) +"&Firstparametername"+Value+"&secondparametername"+Value

it will be like this

http://Machinename/reportserver/(MyReports/My repor&Firstparametername=Value1&secondparametername=Value

|||

Thanks for your reply.

Normal query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. ReportServer won't accept them, I think it doesn't like the way I'm trying to quote them...

Thanks and best regards,
Thomas

|||

Hi Tamer,

I was reading your question and i have the same problem.

Please, tell me, Have you resolved this issue ?

email me to :megch00@.hotmail.com ormanuelgo@.cr-dss.com

Thanks a lot !!

|||

Hi there!

It was indeed quoting related, I must have confused hex with decimal notation in my earlier attempts.

All you have to do is urlencode the parameter names and values. Here is how to quote correctly:

InputEncoded[%5B]%5D&%26spaces+ or%20

Or you let ASP.Net do the job:

1string encodedParam = HttpContext.Current.Server.UrlEncode(myParam);

So ...&Region=[foo].[bar 123].&[baz]... becomes...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D...

If you want this parameter to have multiple values, just use it multiple times in your URL: ...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bheureka%5D...

HTH and best regards,
Thomas

sql

Passing NULL value not working

I currently have a stored procedure that looks something like this

SELECT * FROM tblQuestions WHERE Title LIKE ISNULL('%'+@.Name+'%', Title)

I have a form that supplies this value. This statement should make it so that if a NULL value is passed in, then it will return all the rows, if some text is specified, then it will not. On my SQLDataSource on the page where the parameter is supplied I have set ConvertEmptyStringsToNull to True, but when I say in my code,

SqlDataSource1.SelectParameters.Add("@.Name", TextBox1.Text);

It won't give me back any of the rows, I know that the stored procedure works fine because I can get it to work by a basic query and other testing on it, so somewhere in my form, the NULL value isn't being passed, I belive that it is passing an empty string and I don't know why. Thank you in advance

/jcarver

Try this:

WHERE ([Title] Like '%' + @.Name + '%') or ( @.Name is NULL)"

And add this to your SqlDatasource: CancelSelectOnNullParameter="False"

|||

The two where clauses are logically equivalent.

I'm a bit curious why you have a column called title and a parameter called @.name instead of @.title.

As for passing in a null, you could check the length of the textbox text, and if it's 0, send DBValue instead of the Text property.

|||

Try

IF @.Name IS NULL SET @.Name = ''

IF DATALENGTH(@.Name) > 0
SELECT * FROM tblQuestions WHERE Title LIKE '%'+@.Name+'%'
ELSE
SELECT * FROM tblQuestions

|||

SELECT * FROM tblQuestions WHERE Title LIKE '%' + @.Name + '%'

ConvertEmptyStringToNull must be set to False!

sql

passing null report parameter via a url

Hi,
I have a SP in sql with parameters that can get null values such as:
@.paramguf tinyint=null,
AS
select 1 as kodKibuz,name
from learningstuds
where
(@.paramguf is null or hasamaguf=@.paramguf)
I am sending the report parameters via vb.net
Dim URL As String
URL = "http://localhost/reportserver/reports/tutorials?%2fTutorials%2fKibuzKlita1"
URL += "&rs:Command=render&rc:Parameters=false&rc:Toolbar=false"
URL += "¶mdate=" + Date32.saveDate().ToShortDateString
URL += "¶mguf=" + ddlGuf.SelectedValue
'Pass the URL as a Web request
'Dim request As HttpWebRequest = WebRequest.Create(URL)
Response.Redirect(URL)
How can I send a null value to the report for paramguf?
Thanksquoting KB article http://support.microsoft.com/kb/842853
You can also pass a null value for a parameter to a report by adding
'param:isnull=true' to the URL. For example, if the SalesOrderNumber is a
Null-valued report parameter that must be passed to the report, add the
'SalesOrderNumber:isnull=true' string to the URL."
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"collie" <collie@.discussions.microsoft.com> wrote in message
news:72BB20CA-9B8F-48F6-A3C8-6CCA1049EBE6@.microsoft.com...
> Hi,
> I have a SP in sql with parameters that can get null values such as:
> @.paramguf tinyint=null,
> AS
> select 1 as kodKibuz,name
> from learningstuds
> where
> (@.paramguf is null or hasamaguf=@.paramguf)
> I am sending the report parameters via vb.net
> Dim URL As String
> URL => "http://localhost/reportserver/reports/tutorials?%2fTutorials%2fKibuzKlita1"
> URL += "&rs:Command=render&rc:Parameters=false&rc:Toolbar=false"
> URL += "¶mdate=" + Date32.saveDate().ToShortDateString
> URL += "¶mguf=" + ddlGuf.SelectedValue
> 'Pass the URL as a Web request
> 'Dim request As HttpWebRequest = WebRequest.Create(URL)
> Response.Redirect(URL)
>
> How can I send a null value to the report for paramguf?
> Thanks
>

Wednesday, March 28, 2012

Passing multiple-values as a parameter

I am trying to drill through to a secondary report passing multiple values as a parameter.

For eg. I would like the user to select multiple years (2005,2006) from the drop-down parameter and pass that on to a secondary report.

When I select multiple values and pass that as a parameter, the parameter in the second report defaults to 'All' or in some case the first of the multiple parameters (i.e only 2005 is passed).

I don't have any problems passing single valued parameters. Any help would be appreciated.

FYI - I am using an Analysis Cube as the data source for the report.

Thanks

You can do this by using the Split function, search this forum there are plenty of examples of how to use it.

e.g. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=227106&SiteID=1

|||

Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=164056&SiteID=1

-- Robert

|||Thanks, I got this to work.

passing multiple values to a stored procedure

hi
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
'
thanks
redDo you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:
> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name like
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> '
> thanks
> red|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
--
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
> > hi
> >
> > my query in the stored proc is like ::
> >
> > select * from tableA where name like @.name
> > where @. name is varchar type
> >
> > but the input i get from a list box(where multiple values can be
selected)
> >
> > i think i have to write the query like
> > selct * from tableA where name like @.name1 or name like @.name2 or name
like
> > @.name3...
> >
> > but the problem is that i have no idea before hand to tell how many
values
> > will be sent as params...
> >
> > can some one help me
> > '
> >
> > thanks
> > red
>|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)
thanks
red
"Simon Worth" wrote:
> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> > meant LIKE '%'+@.name+'%'
> >
> > Anyway, if you are using = it will be much more efficient than LIKE if you
> > have an index on name. And you can pass in a (limited) comma-separated
> list
> > and translate it to a table, and join on that table. See
> > http://www.aspfaq.com/2248 for an example.
> >
> > A
> >
> >
> > On 3/3/05 8:25 PM, in article
> > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > <red60man@.discussions.microsoft.com> wrote:
> >
> > > hi
> > >
> > > my query in the stored proc is like ::
> > >
> > > select * from tableA where name like @.name
> > > where @. name is varchar type
> > >
> > > but the input i get from a list box(where multiple values can be
> selected)
> > >
> > > i think i have to write the query like
> > > selct * from tableA where name like @.name1 or name like @.name2 or name
> like
> > > @.name3...
> > >
> > > but the problem is that i have no idea before hand to tell how many
> values
> > > will be sent as params...
> > >
> > > can some one help me
> > > '
> > >
> > > thanks
> > > red
> >
>
>|||Did you see my post? WHY ARE YOU YELLING?
--
http://www.aspfaq.com/
(Reverse address to reply.)
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> > selct * from tableA where name IN (@.name1, @.name2,
@.name3,........@.nameN)
> thanks
> red
> "Simon Worth" wrote:
> > You could try passing the selected text fields back as comma seporated
> > values, and then you can do an IN statement
> > something like
> > selct * from tableA where name IN (@.name1, @.name2, @.name3)
> >
> > That assumes that you really didn't want to use the like statement as in
> > your example you didn't provide any type of wildcard character
> >
> > --
> > Simon Worth
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > > Do you really mean LIKE @.name? This is the same as = @.name. Maybe
you
> > > meant LIKE '%'+@.name+'%'
> > >
> > > Anyway, if you are using = it will be much more efficient than LIKE if
you
> > > have an index on name. And you can pass in a (limited)
comma-separated
> > list
> > > and translate it to a table, and join on that table. See
> > > http://www.aspfaq.com/2248 for an example.
> > >
> > > A
> > >
> > >
> > > On 3/3/05 8:25 PM, in article
> > > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > > <red60man@.discussions.microsoft.com> wrote:
> > >
> > > > hi
> > > >
> > > > my query in the stored proc is like ::
> > > >
> > > > select * from tableA where name like @.name
> > > > where @. name is varchar type
> > > >
> > > > but the input i get from a list box(where multiple values can be
> > selected)
> > > >
> > > > i think i have to write the query like
> > > > selct * from tableA where name like @.name1 or name like @.name2 or
name
> > like
> > > > @.name3...
> > > >
> > > > but the problem is that i have no idea before hand to tell how many
> > values
> > > > will be sent as params...
> > > >
> > > > can some one help me
> > > > '
> > > >
> > > > thanks
> > > > red
> > >
> >
> >
> >|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> > HI,
> >
> > BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
> THERE
> > SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> > > selct * from tableA where name IN (@.name1, @.name2,
> @.name3,........@.nameN)
> >
> > thanks
> > red
> >
> > "Simon Worth" wrote:
> >
> > > You could try passing the selected text fields back as comma seporated
> > > values, and then you can do an IN statement
> > > something like
> > > selct * from tableA where name IN (@.name1, @.name2, @.name3)
> > >
> > > That assumes that you really didn't want to use the like statement as
in
> > > your example you didn't provide any type of wildcard character
> > >
> > > --
> > > Simon Worth
> > >
> > >
> > > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > > news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > > > Do you really mean LIKE @.name? This is the same as = @.name. Maybe
> you
> > > > meant LIKE '%'+@.name+'%'
> > > >
> > > > Anyway, if you are using = it will be much more efficient than LIKE
if
> you
> > > > have an index on name. And you can pass in a (limited)
> comma-separated
> > > list
> > > > and translate it to a table, and join on that table. See
> > > > http://www.aspfaq.com/2248 for an example.
> > > >
> > > > A
> > > >
> > > >
> > > > On 3/3/05 8:25 PM, in article
> > > > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > > > <red60man@.discussions.microsoft.com> wrote:
> > > >
> > > > > hi
> > > > >
> > > > > my query in the stored proc is like ::
> > > > >
> > > > > select * from tableA where name like @.name
> > > > > where @. name is varchar type
> > > > >
> > > > > but the input i get from a list box(where multiple values can be
> > > selected)
> > > > >
> > > > > i think i have to write the query like
> > > > > selct * from tableA where name like @.name1 or name like @.name2 or
> name
> > > like
> > > > > @.name3...
> > > > >
> > > > > but the problem is that i have no idea before hand to tell how
many
> > > values
> > > > > will be sent as params...
> > > > >
> > > > > can some one help me
> > > > > '
> > > > >
> > > > > thanks
> > > > > red
> > > >
> > >
> > >
> > >
>|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:
> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> > BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
> THERE
> > SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
>
>

Passing Multiple GUIDs

Trying to build a report that will return information on many
contacts.
SELECT ContactExtensionBase.ContactId, ContactBase.LastName,
FROM ContactExtensionBase INNER JOIN
ContactBase ON ContactExtensionBase.ContactId = ContactBase.ContactId
WHERE (ContactBase.ContactId = @.cid)
where @.cid is the guid's
But with it like this i can't pass more than one guid into the
statement.Try using the "IN" clause so your WHERE statement looks like:
WHERE ContactBase.ContactId IN(@.cid)
If your parameter is set to accept multiple values then this will retrun ALL
values you select.
Michael
"flowk1337@.gmail.com" wrote:
> Trying to build a report that will return information on many
> contacts.
> SELECT ContactExtensionBase.ContactId, ContactBase.LastName,
> FROM ContactExtensionBase INNER JOIN
> ContactBase ON ContactExtensionBase.ContactId => ContactBase.ContactId
> WHERE (ContactBase.ContactId = @.cid)
>
> where @.cid is the guid's
>
> But with it like this i can't pass more than one guid into the
> statement.
>|||On Apr 27, 4:02 pm, Michael C <Michael C...@.discussions.microsoft.com>
wrote:
> Try using the "IN" clause so your WHERE statement looks like:
> WHERE ContactBase.ContactId IN(@.cid)
> If your parameter is set to accept multiple values then this will retrun ALL
> values you select.
> Michael
>
> "flowk1...@.gmail.com" wrote:
> > Trying to build a report that will return information on many
> > contacts.
> > SELECT ContactExtensionBase.ContactId, ContactBase.LastName,
> > FROM ContactExtensionBase INNER JOIN
> > ContactBase ON ContactExtensionBase.ContactId => > ContactBase.ContactId
> > WHERE (ContactBase.ContactId = @.cid)
> > where @.cid is the guid's
> > But with it like this i can't pass more than one guid into the
> > statement.- Hide quoted text -
> - Show quoted text -
I've already tried that actually. It gives an error that a I can't
convert from a guid to a string.|||Is the convertion error a matter of Collation? If so you may want to try and
COLLATE your join statement. I just can't see why a guid couldn't convert to
a string.
INNER JOIN
ContactBase ON ContactExtensionBase.ContactId COLLATE
SQL_Latin1_General_CP1_CI_AS = ContactBase.ContactId
have you tried to CAST or CONVERT the guid?
"flowk1337@.gmail.com" wrote:
> On Apr 27, 4:02 pm, Michael C <Michael C...@.discussions.microsoft.com>
> wrote:
> > Try using the "IN" clause so your WHERE statement looks like:
> >
> > WHERE ContactBase.ContactId IN(@.cid)
> >
> > If your parameter is set to accept multiple values then this will retrun ALL
> > values you select.
> >
> > Michael
> >
> >
> >
> > "flowk1...@.gmail.com" wrote:
> > > Trying to build a report that will return information on many
> > > contacts.
> >
> > > SELECT ContactExtensionBase.ContactId, ContactBase.LastName,
> > > FROM ContactExtensionBase INNER JOIN
> > > ContactBase ON ContactExtensionBase.ContactId => > > ContactBase.ContactId
> > > WHERE (ContactBase.ContactId = @.cid)
> >
> > > where @.cid is the guid's
> >
> > > But with it like this i can't pass more than one guid into the
> > > statement.- Hide quoted text -
> >
> > - Show quoted text -
> I've already tried that actually. It gives an error that a I can't
> convert from a guid to a string.
>|||On Apr 27, 5:56 pm, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Is the convertion error a matter of Collation? If so you may want to try and
> COLLATE your join statement. I just can't see why a guid couldn't convert to
> a string.
> INNER JOIN
> ContactBase ON ContactExtensionBase.ContactId COLLATE
> SQL_Latin1_General_CP1_CI_AS => ContactBase.ContactId
> have you tried to CAST or CONVERT the guid?
>
> "flowk1...@.gmail.com" wrote:
> > On Apr 27, 4:02 pm, Michael C <Michael C...@.discussions.microsoft.com>
> > wrote:
> > > Try using the "IN" clause so your WHERE statement looks like:
> > > WHERE ContactBase.ContactId IN(@.cid)
> > > If your parameter is set to accept multiple values then this will retrun ALL
> > > values you select.
> > > Michael
> > > "flowk1...@.gmail.com" wrote:
> > > > Trying to build a report that will return information on many
> > > > contacts.
> > > > SELECT ContactExtensionBase.ContactId, ContactBase.LastName,
> > > > FROM ContactExtensionBase INNER JOIN
> > > > ContactBase ON ContactExtensionBase.ContactId => > > > ContactBase.ContactId
> > > > WHERE (ContactBase.ContactId = @.cid)
> > > > where @.cid is the guid's
> > > > But with it like this i can't pass more than one guid into the
> > > > statement.- Hide quoted text -
> > > - Show quoted text -
> > I've already tried that actually. It gives an error that a I can't
> > convert from a guid to a string.- Hide quoted text -
> - Show quoted text -
I found out how to fix my problem. Thanks for your help Michael.
I created a stored procedure with the following:
DECLARE @.GUIDV VARCHAR(7000)
SELECT @.GUIDV = '''' + REPLACE(@.GUIDS , ',', '''' + ',' + '''')
+ ''''
EXECUTE('SELECT ContactExtensionBase.ContactId,
ContactExtensionBase.New_DLNumber, ContactBase.LastName,
ContactBase.MiddleName, ContactBase.FirstName, ContactBase.BirthDate,
ContactBase.MobilePhone, ContactBase.Pager, ContactBase.Telephone1,
ContactBase.StateCode,ContactExtensionBase.New_ReferralName,
ContactBase.Telephone2, ContactBase.Telephone3 FROM
ContactExtensionBase INNER JOIN
ContactBase ON ContactExtensionBase.ContactId =ContactBase.ContactId WHERE ContactId IN (' + @.GUIDV + ')')

Monday, March 26, 2012

Passing LinkButton text value as Sql Query parameter!

Hi,

Could you inform me programmatically how can I pass LinkButton text value as Sql Query parameter? Big Smile

I tried the

1command.CommandText ="SELECT DISTINCT [Description] FROM [Projects] WHERE ([Type] = " & SqlDbType.Text = LinkButton12.Text &")"

but it does not work!!!

 Thanks in advance!!!!

Try changing the line to this:

command.CommandText = "SELECT DISTINCT [Description] FROM [Projects] WHERE ([Type] = '" & LinkButton12.Text & "')"

The statement "SqlDbType.Text = LinkButton12.Text" probably evaluated to a boolean which threw off your query.|||Ok,thank you very much ZeroDefinition... Smile

Passing Integer list to SQL using TableAdapter?

I want to select rows that have an integer value IN a parameter list value

I want to pass an integer list to my tableadapter's select

as in Select * from sometable

Where myIntValue IN (@.intParamList )

I cannot figure out how to get my list into @.intParamList

It works fine as a single value - eg 3, but how do I set it to 1,2,3 ?

Thanks

Bill

Hi,

I don't think you can't pass 1,2,3 as integer parameter according to my knowledge. Well you might have to change the parameter to varchar and it will work for you.

Thanks and best regards,

|||

Thanks I got it.

The answer is to use a varchar list AND a Numbers table which allows me to Select the values without using IN

Googling SQL Numbers table gives several examples

Bill

Passing Information between SQL Agent Job Steps

Is it possible to passing information between SQL Agent Job Steps?
Such as the following job that has 2 tsql steps.
Step 1) Trans sql select * from myTable
Step 2) send result from Step 1 using CDO.
For the example you listed, one option is on the first step you can
have the results of this output to a file. You do this by selecting
the step, go to the advanced tab and then put in the path and file
name for the Output file.
Then on Step 2, you can send the file from step 1.
-Sue
On Mon, 11 Oct 2004 17:51:02 +0100, "Robin" <robin9876@.hotmail.com>
wrote:

>Is it possible to passing information between SQL Agent Job Steps?
>Such as the following job that has 2 tsql steps.
>Step 1) Trans sql select * from myTable
>Step 2) send result from Step 1 using CDO.
>
sql

Passing Information between SQL Agent Job Steps

Is it possible to passing information between SQL Agent Job Steps?
Such as the following job that has 2 tsql steps.
Step 1) Trans sql select * from myTable
Step 2) send result from Step 1 using CDO.
Perhaps you could use a global temp table (##).
Keith
"Robin" <robin9876@.hotmail.com> wrote in message
news:uWFWrJ7rEHA.1988@.TK2MSFTNGP09.phx.gbl...
> Is it possible to passing information between SQL Agent Job Steps?
> Such as the following job that has 2 tsql steps.
> Step 1) Trans sql select * from myTable
> Step 2) send result from Step 1 using CDO.
>
|||Besides Keith's answer, you have anther possibility. You can use DTS, store
results of a T-SQL task in a global variable and send mail using Sed Mail
task or ActiveX script with CDO.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Robin" <robin9876@.hotmail.com> wrote in message
news:uWFWrJ7rEHA.1988@.TK2MSFTNGP09.phx.gbl...
> Is it possible to passing information between SQL Agent Job Steps?
> Such as the following job that has 2 tsql steps.
> Step 1) Trans sql select * from myTable
> Step 2) send result from Step 1 using CDO.
>

passing in long parametes to reporting services

hello,
how can i pass in long parameter into a report?
if i were to use url its just too short.
i want my users to select tons of numbers and pass it to RS. url parameter
access just does not seems right. does anyone have any advice for me?You could store the multiple values as rows in a db table, and assign a key
value which groups these together for the session. Then pass the key value
in as a parameter, the query could then use the key , and join the params
table to the orig table...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asha" <Asha@.discussions.microsoft.com> wrote in message
news:F2C123E8-3FA7-458A-863A-BC95FD4BF1B5@.microsoft.com...
> hello,
> how can i pass in long parameter into a report?
> if i were to use url its just too short.
> i want my users to select tons of numbers and pass it to RS. url parameter
> access just does not seems right. does anyone have any advice for me?

Wednesday, March 21, 2012

Passing comma delimited parameter to SP

Is this possible? I find it hard to believe that this could be sooo difficult. I have a simple select stored procedure that has one parameter. My application is passing a comma delimited string of values to be used in the IN clause.

Ex: Where x In(@.parametername)

the x column is an integer. How can one work around this?

Thanks! Storm [st]

http://www.sommarskog.se/arrays-in-sql.html

Passing arrays as parameter (SqlDataSource)

My sql-string looks like this:

 SelectCommand="SELECT *FROM Table1WHERE Field1IN @.target"

And my parameter looks like this:

<asp:ControlParameter Name="target" ControlID="CheckBoxList1" PropertyName="SelectedValue" />

This code gives me a syntax error near @.target. Someone got a solution?

I wish I new how to do it with a Parameter, but I'm running into the same problem as you. However, here's an example on how to do it by simple modifying the CommandText during the SqlDataSource.Selecting event:

ASPX

<asp:checkboxlist id="lstProducts" runat="server" datasourceid="sdsProducts" datatextfield="ProductName"datavaluefield="ProductID" autopostback="True" onselectedindexchanged="lstProducts_SelectedIndexChanged"></asp:checkboxlist><asp:sqldatasource id="sdsProducts" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"selectcommand="SELECT TOP 10 ProductID, ProductName FROM Products"></asp:sqldatasource><br /><asp:gridview id="gvProducts" runat="server" datakeynames="ProductID" datasourceid="sdsProducts2"></asp:gridview><asp:sqldatasource id="sdsProducts2" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"onselecting="sdsProducts2_Selecting" selectcommand="SELECT * FROM Products"></asp:sqldatasource>

CODE-BEHIND

protected void sdsProducts2_Selecting(object sender, SqlDataSourceSelectingEventArgs e){if (this.IsPostBack){e.Command.CommandText = String.Format("SELECT * FROM Products WHERE ProductID IN ({0})",this.GetInExpression(lstProducts));}else{e.Cancel =true;}}private string GetInExpression(ListControl control){List<string> list =new List<string>();foreach (ListItem itemin control.Items){if (item.Selected){list.Add(item.Value);}}return String.Join(", ", list.ToArray());}protected void lstProducts_SelectedIndexChanged(object sender, EventArgs e){gvProducts.DataBind();}

|||There must be a more simple way to do this?

Tuesday, March 20, 2012

Passing an Array to a Stored Procedures

How to do this ?

==============================
CREATE procedure dbo.AddTb2FromTb1
@.Tb1No nvarchar(1000)
as
insert into Tb2 (*)
select * from Tb1
where Tb1 IN (@.Tb1No) /* How to Passing an Array to a Stored Procedures ? */
==============================

dbo.AddTb2FromTb1 'No001' is Work !
dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !If you are using SQL 2000 you can create a user-defined fnction - have a look at the following link:

Sql Team|||I Try CnvToChar() is work...

select * from CsvToChar('PO001234,PO123') is work

CharValue
-----
PO001234
Po123
-----

but,

=====================================================
select * from Tb1 Where TbNo IN (select * from CsvToChar('PO001234,PO123'))
have error message:

Server: Message 446, Level 16, Status 9, Line 1
can not to analyze 'equal to' action order collide.
=====================================================

thank you help...|||My SQL Server is Chinese System,
Character Record Fields in Table is default 'COLLATE Chinese_Taiwan_Stroke_BIN' vaule,
so i add the Default value in the function CvsTochar() return value.

==============================================
CREATE Function dbo.CsvToChar ( @.Array varchar(1000))
returns @.CharTable table
(CharValue char(10) COLLATE Chinese_Taiwan_Stroke_BIN)
AS
begin

declare @.separator char(1)
set @.separator = ','
.........
.........
===============================================

No error message to display, It's work... ^_^ Y

Ehorn - Thank you very much !!!

Johnny SCB

Passing a subquery as a parameter to a user defined function

Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a tabl
e.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>|||I have a function which accepts a string as a parameter and returns a table.
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.|||Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a tabl
e.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>

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.

Friday, March 30, 2012

Passing parameter in LIKE statement with '%'

Passing Paramenter to SP for Column Name

Passing OLAP Parameters Within a URL

Passing NULL value not working

passing null report parameter via a url

Wednesday, March 28, 2012

Passing multiple-values as a parameter

passing multiple values to a stored procedure

passing multiple values to a stored procedure

passing multiple values to a stored procedure

Passing Multiple GUIDs

Monday, March 26, 2012

Passing LinkButton text value as Sql Query parameter!

Passing Integer list to SQL using TableAdapter?

Passing Information between SQL Agent Job Steps

Passing Information between SQL Agent Job Steps

passing in long parametes to reporting services

Wednesday, March 21, 2012

Passing comma delimited parameter to SP

Passing arrays as parameter (SqlDataSource)

Tuesday, March 20, 2012

Passing an Array to a Stored Procedures

Passing a subquery as a parameter to a user defined function

Passing a string for IN clause

Partitions and FileGroups

Blog Archive

About Me