Multi Value Parameters

I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.

Code Snippet

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

This should help you:

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

See Lisa's posts.

You can't just use IN (@.Region). SQL doesn't automatically understand that. Developing this can be extemely frustrating, but don't despair. We're here to help if you get stuck.

|||

I also faced same problem.In reporting service, even though you select multi values for parameter and send it to stored procedure,it wil go as a single Value.

U got it.In stored procedure,if u included where clause with IN,it wil consider it as a single value only(as 'a,b,c,d').

So, u need to split this (comma seperated).You have to include split function into ur stored procedure.Search in google, u wil get it.

I same i used in my report and its working fine.Its very easy.

|||

There's a reat article on what you need to do here:

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Working_with_Multivalued_Parameters

including some copy and paste code.

Enjoy!

-Eric

Multi Value Parameters

I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.

Code Snippet

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

This should help you:

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

See Lisa's posts.

You can't just use IN (@.Region). SQL doesn't automatically understand that. Developing this can be extemely frustrating, but don't despair. We're here to help if you get stuck.

|||

I also faced same problem.In reporting service, even though you select multi values for parameter and send it to stored procedure,it wil go as a single Value.

U got it.In stored procedure,if u included where clause with IN,it wil consider it as a single value only(as 'a,b,c,d').

So, u need to split this (comma seperated).You have to include split function into ur stored procedure.Search in google, u wil get it.

I same i used in my report and its working fine.Its very easy.

|||

There's a reat article on what you need to do here:

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Working_with_Multivalued_Parameters

including some copy and paste code.

Enjoy!

-Eric

Multi value parameters

Can anyone give me the syntax to pass mutliple parameters into a stored
procedure? I know that you have to use IN, but I cannot get it to work. Thank
you in advance.Create your Dataset, select the Proc and execute it in Reporting Services
Designer. It will create your parameters for you, then you can go into the
Report > Parameters section and adjust the settings from there. What are you
using IN for? Can you post some code that you are using?
"KimB" wrote:
> Can anyone give me the syntax to pass mutliple parameters into a stored
> procedure? I know that you have to use IN, but I cannot get it to work. Thank
> you in advance.|||As a side note, RS 2005 supports multi-value parameters natively, so no need
of an expression-based query.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"KimB" <KimB@.discussions.microsoft.com> wrote in message
news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> Can anyone give me the syntax to pass mutliple parameters into a stored
> procedure? I know that you have to use IN, but I cannot get it to work.
> Thank
> you in advance.|||I'm trying to pass a multi-value to a stored procedure as well but it isn't
working. The documentation says that using a SP with multi-value isn't
supported. Huh? Most of my reports use SPs.
Can this be done? Is there a workaround?
This was the #1 feature I was waiting for in RS2005.
Thanks in advance!
"Teo Lachev [MVP]" wrote:
> As a side note, RS 2005 supports multi-value parameters natively, so no need
> of an expression-based query.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > procedure? I know that you have to use IN, but I cannot get it to work.
> > Thank
> > you in advance.
>
>|||What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> I'm trying to pass a multi-value to a stored procedure as well but it
> isn't
> working. The documentation says that using a SP with multi-value isn't
> supported. Huh? Most of my reports use SPs.
> Can this be done? Is there a workaround?
> This was the #1 feature I was waiting for in RS2005.
> Thanks in advance!
>
> "Teo Lachev [MVP]" wrote:
>> As a side note, RS 2005 supports multi-value parameters natively, so no
>> need
>> of an expression-based query.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "KimB" <KimB@.discussions.microsoft.com> wrote in message
>> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
>> > Can anyone give me the syntax to pass mutliple parameters into a stored
>> > procedure? I know that you have to use IN, but I cannot get it to work.
>> > Thank
>> > you in advance.
>>|||I understand now-- thanks-- and this worked great for me.
It's too bad the documentation doesn't provide this simple information given
that the multi-value feature is bound to be a highly-desired feature.
Thanks again!
"Bruce L-C [MVP]" wrote:
> What doesn't work has nothing really to do with RS but has to do with Stored
> Procedures in SQL Server. You cannot do the following in a stored procedure.
> Let's say you have a Parameter called @.MyParams
> Now you can map that parameter to a multi-value parameter but if in your
> stored procedure you try to do this:
> select * from sometable where somefield in (@.MyParams)
> It won't work. Try it. Create a stored procedure and try to pass a
> multi-value parameter to the stored procedure. It won't work.
> What you can do is to have a string parameter that is passed as a multivalue
> parameter and then change the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> GO
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > I'm trying to pass a multi-value to a stored procedure as well but it
> > isn't
> > working. The documentation says that using a SP with multi-value isn't
> > supported. Huh? Most of my reports use SPs.
> >
> > Can this be done? Is there a workaround?
> >
> > This was the #1 feature I was waiting for in RS2005.
> >
> > Thanks in advance!
> >
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> As a side note, RS 2005 supports multi-value parameters natively, so no
> >> need
> >> of an expression-based query.
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> >> > procedure? I know that you have to use IN, but I cannot get it to work.
> >> > Thank
> >> > you in advance.
> >>
> >>
> >>
>
>|||I've been using the charlist_to_table function to return my parameters in a
table and it works well but I can't get it to return more than 800 rows--
even if the string sent contains more than 800 entries.
I checked and RS is sending ALL of the selected entries (868 in this case)
but the function is only returning 800 rows.
I used a similiar function from elsewhere on the net and it also returned
only 800 rows.
Is anyone aware of a limitation here-- I don't see any limitation in the
function?
Thanks!
"Michael Creamer" wrote:
> I understand now-- thanks-- and this worked great for me.
> It's too bad the documentation doesn't provide this simple information given
> that the multi-value feature is bound to be a highly-desired feature.
> Thanks again!
>
> "Bruce L-C [MVP]" wrote:
> > What doesn't work has nothing really to do with RS but has to do with Stored
> > Procedures in SQL Server. You cannot do the following in a stored procedure.
> >
> > Let's say you have a Parameter called @.MyParams
> >
> > Now you can map that parameter to a multi-value parameter but if in your
> > stored procedure you try to do this:
> >
> > select * from sometable where somefield in (@.MyParams)
> >
> > It won't work. Try it. Create a stored procedure and try to pass a
> > multi-value parameter to the stored procedure. It won't work.
> >
> > What you can do is to have a string parameter that is passed as a multivalue
> > parameter and then change the string into a table.
> >
> > This technique was told to me by SQL Server MVP, Erland Sommarskog
> > For example I have done this
> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >
> > So note this is NOT an issue with RS, it is strictly a stored procedure
> > issue.
> >
> > Here is the function:
> > CREATE FUNCTION charlist_to_table
> > (@.list ntext,
> > @.delimiter nchar(1) = N',')
> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > str varchar(4000),
> > nstr nvarchar(2000)) AS
> >
> > BEGIN
> > DECLARE @.pos int,
> > @.textpos int,
> > @.chunklen smallint,
> > @.tmpstr nvarchar(4000),
> > @.leftover nvarchar(4000),
> > @.tmpval nvarchar(4000)
> >
> > SET @.textpos = 1
> > SET @.leftover = ''
> > WHILE @.textpos <= datalength(@.list) / 2
> > BEGIN
> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > SET @.textpos = @.textpos + @.chunklen
> >
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >
> > WHILE @.pos > 0
> > BEGIN
> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > END
> >
> > SET @.leftover = @.tmpstr
> > END
> >
> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > ltrim(rtrim(@.leftover)))
> > RETURN
> > END
> >
> > GO
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > > I'm trying to pass a multi-value to a stored procedure as well but it
> > > isn't
> > > working. The documentation says that using a SP with multi-value isn't
> > > supported. Huh? Most of my reports use SPs.
> > >
> > > Can this be done? Is there a workaround?
> > >
> > > This was the #1 feature I was waiting for in RS2005.
> > >
> > > Thanks in advance!
> > >
> > >
> > > "Teo Lachev [MVP]" wrote:
> > >
> > >> As a side note, RS 2005 supports multi-value parameters natively, so no
> > >> need
> > >> of an expression-based query.
> > >>
> > >> --
> > >> HTH,
> > >> ---
> > >> Teo Lachev, MVP, MCSD, MCT
> > >> "Microsoft Reporting Services in Action"
> > >> "Applied Microsoft Analysis Services 2005"
> > >> Home page and blog: http://www.prologika.com/
> > >> ---
> > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > >> > procedure? I know that you have to use IN, but I cannot get it to work.
> > >> > Thank
> > >> > you in advance.
> > >>
> > >>
> > >>
> >
> >
> >|||It is working now.
I had been using a similar function, UtilSplit, but I've now switched to the
charlist_to_table function and all the rows are being returned correctly.
"Michael Creamer" wrote:
> I've been using the charlist_to_table function to return my parameters in a
> table and it works well but I can't get it to return more than 800 rows--
> even if the string sent contains more than 800 entries.
> I checked and RS is sending ALL of the selected entries (868 in this case)
> but the function is only returning 800 rows.
> I used a similiar function from elsewhere on the net and it also returned
> only 800 rows.
> Is anyone aware of a limitation here-- I don't see any limitation in the
> function?
> Thanks!
> "Michael Creamer" wrote:
> > I understand now-- thanks-- and this worked great for me.
> >
> > It's too bad the documentation doesn't provide this simple information given
> > that the multi-value feature is bound to be a highly-desired feature.
> >
> > Thanks again!
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What doesn't work has nothing really to do with RS but has to do with Stored
> > > Procedures in SQL Server. You cannot do the following in a stored procedure.
> > >
> > > Let's say you have a Parameter called @.MyParams
> > >
> > > Now you can map that parameter to a multi-value parameter but if in your
> > > stored procedure you try to do this:
> > >
> > > select * from sometable where somefield in (@.MyParams)
> > >
> > > It won't work. Try it. Create a stored procedure and try to pass a
> > > multi-value parameter to the stored procedure. It won't work.
> > >
> > > What you can do is to have a string parameter that is passed as a multivalue
> > > parameter and then change the string into a table.
> > >
> > > This technique was told to me by SQL Server MVP, Erland Sommarskog
> > > For example I have done this
> > > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> > >
> > > So note this is NOT an issue with RS, it is strictly a stored procedure
> > > issue.
> > >
> > > Here is the function:
> > > CREATE FUNCTION charlist_to_table
> > > (@.list ntext,
> > > @.delimiter nchar(1) = N',')
> > > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > > str varchar(4000),
> > > nstr nvarchar(2000)) AS
> > >
> > > BEGIN
> > > DECLARE @.pos int,
> > > @.textpos int,
> > > @.chunklen smallint,
> > > @.tmpstr nvarchar(4000),
> > > @.leftover nvarchar(4000),
> > > @.tmpval nvarchar(4000)
> > >
> > > SET @.textpos = 1
> > > SET @.leftover = ''
> > > WHILE @.textpos <= datalength(@.list) / 2
> > > BEGIN
> > > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > > SET @.textpos = @.textpos + @.chunklen
> > >
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > >
> > > WHILE @.pos > 0
> > > BEGIN
> > > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > > END
> > >
> > > SET @.leftover = @.tmpstr
> > > END
> > >
> > > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > > ltrim(rtrim(@.leftover)))
> > > RETURN
> > > END
> > >
> > > GO
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> > > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > > > I'm trying to pass a multi-value to a stored procedure as well but it
> > > > isn't
> > > > working. The documentation says that using a SP with multi-value isn't
> > > > supported. Huh? Most of my reports use SPs.
> > > >
> > > > Can this be done? Is there a workaround?
> > > >
> > > > This was the #1 feature I was waiting for in RS2005.
> > > >
> > > > Thanks in advance!
> > > >
> > > >
> > > > "Teo Lachev [MVP]" wrote:
> > > >
> > > >> As a side note, RS 2005 supports multi-value parameters natively, so no
> > > >> need
> > > >> of an expression-based query.
> > > >>
> > > >> --
> > > >> HTH,
> > > >> ---
> > > >> Teo Lachev, MVP, MCSD, MCT
> > > >> "Microsoft Reporting Services in Action"
> > > >> "Applied Microsoft Analysis Services 2005"
> > > >> Home page and blog: http://www.prologika.com/
> > > >> ---
> > > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> > > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > > >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > > >> > procedure? I know that you have to use IN, but I cannot get it to work.
> > > >> > Thank
> > > >> > you in advance.
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >|||I had passed on your question to the author of the charlist_to_table
function. I'll tell him nevermind.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:540D9E63-DD7F-478B-89E1-D36B6BF1FC4B@.microsoft.com...
> It is working now.
> I had been using a similar function, UtilSplit, but I've now switched to
> the
> charlist_to_table function and all the rows are being returned correctly.
> "Michael Creamer" wrote:
>> I've been using the charlist_to_table function to return my parameters in
>> a
>> table and it works well but I can't get it to return more than 800 rows--
>> even if the string sent contains more than 800 entries.
>> I checked and RS is sending ALL of the selected entries (868 in this
>> case)
>> but the function is only returning 800 rows.
>> I used a similiar function from elsewhere on the net and it also returned
>> only 800 rows.
>> Is anyone aware of a limitation here-- I don't see any limitation in the
>> function?
>> Thanks!
>> "Michael Creamer" wrote:
>> > I understand now-- thanks-- and this worked great for me.
>> >
>> > It's too bad the documentation doesn't provide this simple information
>> > given
>> > that the multi-value feature is bound to be a highly-desired feature.
>> >
>> > Thanks again!
>> >
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > What doesn't work has nothing really to do with RS but has to do with
>> > > Stored
>> > > Procedures in SQL Server. You cannot do the following in a stored
>> > > procedure.
>> > >
>> > > Let's say you have a Parameter called @.MyParams
>> > >
>> > > Now you can map that parameter to a multi-value parameter but if in
>> > > your
>> > > stored procedure you try to do this:
>> > >
>> > > select * from sometable where somefield in (@.MyParams)
>> > >
>> > > It won't work. Try it. Create a stored procedure and try to pass a
>> > > multi-value parameter to the stored procedure. It won't work.
>> > >
>> > > What you can do is to have a string parameter that is passed as a
>> > > multivalue
>> > > parameter and then change the string into a table.
>> > >
>> > > This technique was told to me by SQL Server MVP, Erland Sommarskog
>> > > For example I have done this
>> > > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> > >
>> > > So note this is NOT an issue with RS, it is strictly a stored
>> > > procedure
>> > > issue.
>> > >
>> > > Here is the function:
>> > > CREATE FUNCTION charlist_to_table
>> > > (@.list ntext,
>> > > @.delimiter nchar(1) = N',')
>> > > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> > > str varchar(4000),
>> > > nstr nvarchar(2000)) AS
>> > >
>> > > BEGIN
>> > > DECLARE @.pos int,
>> > > @.textpos int,
>> > > @.chunklen smallint,
>> > > @.tmpstr nvarchar(4000),
>> > > @.leftover nvarchar(4000),
>> > > @.tmpval nvarchar(4000)
>> > >
>> > > SET @.textpos = 1
>> > > SET @.leftover = ''
>> > > WHILE @.textpos <= datalength(@.list) / 2
>> > > BEGIN
>> > > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> > > SET @.tmpstr = @.leftover + substring(@.list, @.textpos,
>> > > @.chunklen)
>> > > SET @.textpos = @.textpos + @.chunklen
>> > >
>> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > >
>> > > WHILE @.pos > 0
>> > > BEGIN
>> > > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> > > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> > > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > > END
>> > >
>> > > SET @.leftover = @.tmpstr
>> > > END
>> > >
>> > > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> > > ltrim(rtrim(@.leftover)))
>> > > RETURN
>> > > END
>> > >
>> > > GO
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
>> > > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
>> > > > I'm trying to pass a multi-value to a stored procedure as well but
>> > > > it
>> > > > isn't
>> > > > working. The documentation says that using a SP with multi-value
>> > > > isn't
>> > > > supported. Huh? Most of my reports use SPs.
>> > > >
>> > > > Can this be done? Is there a workaround?
>> > > >
>> > > > This was the #1 feature I was waiting for in RS2005.
>> > > >
>> > > > Thanks in advance!
>> > > >
>> > > >
>> > > > "Teo Lachev [MVP]" wrote:
>> > > >
>> > > >> As a side note, RS 2005 supports multi-value parameters natively,
>> > > >> so no
>> > > >> need
>> > > >> of an expression-based query.
>> > > >>
>> > > >> --
>> > > >> HTH,
>> > > >> ---
>> > > >> Teo Lachev, MVP, MCSD, MCT
>> > > >> "Microsoft Reporting Services in Action"
>> > > >> "Applied Microsoft Analysis Services 2005"
>> > > >> Home page and blog: http://www.prologika.com/
>> > > >> ---
>> > > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
>> > > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
>> > > >> > Can anyone give me the syntax to pass mutliple parameters into a
>> > > >> > stored
>> > > >> > procedure? I know that you have to use IN, but I cannot get it
>> > > >> > to work.
>> > > >> > Thank
>> > > >> > you in advance.
>> > > >>
>> > > >>
>> > > >>
>> > >
>> > >
>> > >

multi keyword search SP

Is it possible to write a Stored Procedure that takes a string of
search keywords as argument and returns the recordset? At the mo I am
passing the WHERE String as argument.

I got this technique from an Extreme Ultradev tutorial by Rick Curtis
it looked quite ok:
http://www.princeton.edu/~rcurtis/u...tutorial12.html

I have to admit, one of the main reason for passing the WHERE string is
that I do not know how to do the string splitting / parsing and putting
together in a Stored Procedure. I bet T-SQL would be just as powerful
as VBScript if I just knew it well enough.

What I liked about having built them on the web script was the
flexibility allowing to potentially build an advanced search without
having to change the stored procedure - but this is not crucial I could
always write several stored procedures or add parameters to the SP.

Here is what I have achieved in this way:
User can enter one ore more keywords separated by space.
Search algorithm returns results across a number of fields where ALL
search words are contained in any of these.
Search results will always be formatted a certain way and displayed in
a html table no matter how the search procedure / criteria is varied.

Here is the algorithm (that now works in ASP)
1. split search string into separate keywords
2. build where condition based on single keyword, concatenating all
searched fields (" AND f1+' '+f2+{' '+f<n>} LIKE %<keyword>%")
3. concatenate all these where conditions and pass to stored procedure.
4. stored procedure takes care of all other logic (e.g. Joins, which
fields are searched etc.). It uses a string variable @.SQL to build the
complete search string and then does
execute (@.SQL);
to create the recordset.

I bet there is a way to move 1. 2. and 3. into the SP (and I would feel
better if it was) but I don't have the expertise to do this. If anybody
wants to help me this is very welcome.

I can also post my original code to clarify, just want to avoid too
long posts.

Cheers
AxelThese links might give you some ideas (the first is similar to the
article you linked to, but from a pure TSQL perspective):

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

TSQL string functions are not as powerful as those in other languages,
so if you have lots of string parsing, it would probably be best to do
it in the ASP code rather than in TSQL.

If your requirements become more complex, you could consider some sort
of reporting tool, rather than writing and maintaining code yourself.

Simon|||Thanks Simon

I had actually already read that article but it takes a different
approach - the SP takes a named list of parameters (similar to an
advanced search form) so it presumes the user knows where to look for
the keyword. My approch concatenates all candidate fields into one
large string and then looks for the search string within that long
string. It loops this process with multilpe kewoards and concatenates
using the AND condition.

If for example we assume for simplicities sake there is on table
tblProduct with the fields Name, Desc, Comment, Category, ID and I have
a record with "Iron Screw", "17 inch amalgated Screw - green", "useful
for outdoor use","consumables","1234-23"
this would be caught by passing "screw iron 17 outdoor" to the search
procedure. The user does not need to know in which fields the keywords
are contained but at the same time he will be able to narrow down the
search results very efficiently by adding more search words.
In my little loop I am comparing
'Iron Screw 17 inch amalgated Screw - green useful for outdoor use
consumables 1234-23' Like '%screw%'
AND
'Iron Screw 17 inch amalgated Screw - green useful for outdoor use
consumables 1234-23' Like '%iron%'
AND
'Iron...' Like '%17%'
AND
'Iron...' Like '%outdoor%'

what I need is like a split() and loop in T-SQL; also I would have to
eliminate invalid characters e.g. single quotes for the search string
to avoid any attempts at hacking.

I was told using dynamic SQL the way I have done here is wrong I only
want to get it right. Maybe there is another approach? MOre opinions
welcome...

thx,
Axel|||On 17 Jun 2005 03:56:57 -0700, Axel wrote:

>My approch concatenates all candidate fields into one
>large string and then looks for the search string within that long
>string.
(snip example)

Hi Axel,

The example clarifies what you want to do.

First, revisit this page: http://www.sommarskog.se/arrays-in-sql.html.
It will explain how you can break the collected search words ("screw
iron 17 outdoor" in your example) into a set of rows.

To find the Products that match at least one of the terms given, use

SELECT DISTINCT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s-- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'

And if you only want the products that match ALL the terms given:

SELECT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s-- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'
GROUP BY p.Product
HAVING COUNT(*) = (SELECT COUNT(*) FROM #SearchWords)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Axel (realraven2000@.hotmail.com) writes:
> I had actually already read that article but it takes a different
> approach - the SP takes a named list of parameters (similar to an
> advanced search form) so it presumes the user knows where to look for
> the keyword. My approch concatenates all candidate fields into one
> large string and then looks for the search string within that long
> string. It loops this process with multilpe kewoards and concatenates
> using the AND condition.

If I were you, I would look into full-text search. Not that I know
whether this is the answer to your problem, because I have never
used full-text myself. But I would look into it, before I started
to build a complex engine myself.

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

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

Multi Column FTS (2005) Query

Hi
I am trying to get my query syntax to work given a typical query I expect to
passed into my FTS procedure.
The typical search string could be Italy investment. The FTS must search
for Italy filtering by investment.
With an FTS index on CountryName and Item (item is where the word investment
resides)
Some t-sql like this would do the trick:
SELECT CountryName, ItemName
FROM SearchView
WHERE CONTAINS(*, 'italy AND investment')
Given that a single textbox is displayed to allow users to type their search
criteria, how would one seperate such a query so that country names were
searched against the CountryName FTS column and other (non country names)
searched against the FTS Item column?
Should I have several queries to determine if countries exist? If so, then
apply those country names in the AND of the WHERE clause, then how would I
then parse to remove the country names from the intial query string so that
the remaining words can be searched against the FTS Item column?
To add to the delimea, I'm using the FORMSOF(THESAURUS...) functionality, so
a user can query investment Europe (getting countries in the thesaurus file
matching Europe)
Craig
Unfortunately what you would have to do is the following
select *From SearchView where contains(CountryName, 'Italy') and
contains(ItenName,'investment')
This would offer the best performance, you could also do the following
select * from SearchView where CountryName='Italy' and
contains(ItenName,'investment')
unfortunately you will have to parse the search string to see which
countries are present in it, and then build your query accordingly.
Hilary

> To add to the delimea, I'm using the FORMSOF(THESAURUS...)
functionality, so
> a user can query investment Europe (getting countries in the
thesaurus file
> matching Europe)
> Craig
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Mulitple Queries in a Stored Procedure

I have written a stored procedure that contains queries which return data.
It works correctly in Query Analyzer, returning each of the labled fields.
When I create a report the only return field the report wizard shows is from
the first query. When I add each of the other return fields and run the
report in VS I get an out of index error.RS only supports a single resultset being returned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bassbuster" <bassbuster@.discussions.microsoft.com> wrote in message
news:39FAA817-C491-493D-81BB-FDA153D7DB57@.microsoft.com...
> I have written a stored procedure that contains queries which return data.
> It works correctly in Query Analyzer, returning each of the labled fields.
> When I create a report the only return field the report wizard shows is
> from
> the first query. When I add each of the other return fields and run the
> report in VS I get an out of index error.

mulitiple selects in a stored procedure

Hi,
I am trying to create a pagination within a stored procedure but I need
to select from several tables:
First I am inserting the fields into a temp table and this works fine:
INSERT INTO #TempItems (Name)
SELECT Name FROM tblName
I thought I could get data from other tables using UNION:
INSERT INTO #TempItems (Name,Address,Telephone,Street)
SELECT Name FROM tblName
UNION
SELECT Address FROM tblAddress
UNION
SELECT Telephone FROM tblTelephone
UNION
SELECT Street FROM tblStreet
*These are bogus fields I have used as examples.
But the error I get is that the number of insert fields is less then the
select? Could anyone help on how the best way to achieve this?
many thanks in advance
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You can google the newsgroups (suggest .programming) for pagination
techniques. Your error is due to a misunderstanding of how union works.
Union merely combines separate result sets into one; you are attempting to
use it (incorrectly) as you would a join. Assuming that a union could be
used, you must specify 4 items within the select list of each select
statement that is part of the union, corresponding to the 4 columns to be
inserted. There are other flaws in your logic, but this should get you
started.
You should be inserting using something like the following
insert ...
select ...
from tblName inner join tblAddress on ...
inner join tblTelephone on ...
inner join tblStreet on ...
where ...
How those joins are made (and their type - inner, outer, cross, etc) I
cannot answer without knowing the relationships between the table.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:OoqGZO3rDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to create a pagination within a stored procedure but I need
> to select from several tables:
> First I am inserting the fields into a temp table and this works fine:
> INSERT INTO #TempItems (Name)
> SELECT Name FROM tblName
> I thought I could get data from other tables using UNION:
> INSERT INTO #TempItems (Name,Address,Telephone,Street)
> SELECT Name FROM tblName
> UNION
> SELECT Address FROM tblAddress
> UNION
> SELECT Telephone FROM tblTelephone
> UNION
> SELECT Street FROM tblStreet
> *These are bogus fields I have used as examples.
>
> But the error I get is that the number of insert fields is less then the
> select? Could anyone help on how the best way to achieve this?
> many thanks in advance
> Peter
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

MTS Query

Hi,
This is a silly question
1. What the precausions we need to take care while coding a procedure, if we
have an MTS. Do we need to explicitly mention the Transactions in the sps ?
And what about setting the Value of 'XACT_ABORT' .
2. Another situation in the same.
Normally all our master tables will have a curresponding Audit table. and
what we do usually in case we dont have a transaction server is given below
SET XACT_ABORT ON
step 1. Insert into master table
step 2. Insert into Audit table
step 3. Begin Trans
.........
.........
.........
If @.@.ERROR<> 0
rollback trans
step 4. Commit Trans
Now how can i do the same in case we have a transaction server.
thanks and regards
LaraAFAIK, the behaviour might be the same. forget about new lines or any stuff
"lara169" wrote:

> Hi,
> This is a silly question
> 1. What the precausions we need to take care while coding a procedure, if
we
> have an MTS. Do we need to explicitly mention the Transactions in the sps
?
> And what about setting the Value of 'XACT_ABORT' .
> 2. Another situation in the same.
> Normally all our master tables will have a curresponding Audit table. a
nd
> what we do usually in case we dont have a transaction server is given belo
w
> SET XACT_ABORT ON
> step 1. Insert into master table
> step 2. Insert into Audit table
> step 3. Begin Trans
> .........
> ..........
> ..........
> If @.@.ERROR<> 0
> rollback trans
> step 4. Commit Trans
>
> Now how can i do the same in case we have a transaction server.
> thanks and regards
> Lara
>
>

MSsQL2005; OPENROWSET, BLOB/IMAGE and STORED PROCEDURE problems

All,

I work with Microsoft SQL Server 2005 on windows XP professional.
I'd like to create stored procdure to add image to my database (jpg file).
I managed to do it using VARCHAR variable in stored procedure
and then using EXEC, but it don't work directly.

My Table definiton:
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Login] [char](10),
[Password] [char](20),
[Avatar] [image] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My working solution using stored procedure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
SET @.Query = 'INSERT INTO USERS ' + CHAR(13)
+ 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)
+ '''' + @.Password + ''' AS Password,' + CHAR(13)
+ '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)'
EXECUTE (@.Query)
SET @.UserID = @.@.IDENTITY
END

I'd like to use statement in the stored procdure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
DECLARE
@.Query AS VARCHAR(MAX)

SET @.AvatarFileLocation = 'C:\hitman1.jpg'
INSERT INTO USERS
SELECT @.Login AS Login,
@.Password AS Password,
(SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)

SET @.UserID = @.@.IDENTITY

END

It generates error:
Incorrect syntax near '@.AvatarFileLocation'.

My question is:
Why it does not work and how to write the stored procedure code to run this code without errors.

Thanks for any reply

You can't use a variable inside OPENROWSET.

What you are doing, in any case, IS VERY DANGEROUS. There

are many ways in which dynamic SQL is vulnerable to SQL

injection. Please read about it, so that you don't lose

everything you have when a malicious user joins your

site/forum with a password like

O',0x; delete from USERS where Password <> 'O';return 0;--

You might start reading here:

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

Steve Kass

Drew University

Michal1979@.discussions.microsoft.com wrote:

> All,

>

> I work with Microsoft SQL Server 2005 on windows XP professional.

> I'd like to create stored procdure to add image to my database (jpg

> file).

> I managed to do it using VARCHAR variable in stored procedure

> and then using EXEC, but it don't work directly.

>

> My Table definiton:

> CREATE TABLE [dbo].[Users](

> [UserID] [int] IDENTITY(1,1) NOT NULL,

> [Login] [char](10),

> [Password] [char](20),

> [Avatar] [image] NULL,

> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

> (

> [UserID] ASC

> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

>

> My working solution using stored procedure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> SET @.Query = 'INSERT INTO USERS ' + CHAR(13)

> + 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)

> + '''' + @.Password + ''' AS Password,' + CHAR(13)

> + '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''',

> SINGLE_BLOB) AS OBRAZEK)'

> EXECUTE (@.Query)

> SET @.UserID = @.@.IDENTITY

> END

>

> I'd like to use statement in the stored procdure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> DECLARE

> @.Query AS VARCHAR(MAX)

>

> SET @.AvatarFileLocation = 'C:\hitman1.jpg'

> INSERT INTO USERS

> SELECT @.Login AS Login,

> @.Password AS Password,

> (SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS

> OBRAZEK)

>

>

> SET @.UserID = @.@.IDENTITY

>

> END

>

>

> It generates error:

> Incorrect syntax near '@.AvatarFileLocation'.

>

> My question is:

> Why it does not work and how to write the stored procedure code to run

> this code without errors.

>

> Thanks for any reply

>

>

>

>|||

NNTP User

Lot thanks for the repy,

The article link is very interesting.

I understand risks from using dynamic sql, but I'm not goiing to allow user call my stored procedure directly.

All arguments of the stored procedure will be validated and constructed my my application.

My real question is why can I use OPENROWSET function in stored procedure when I build the query

using varchar variable and then execute it, although I can't do it direclty using OPENROWSET in

stored procedure.

|||You cannot use a variable like this:

OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB)

The file name must be hard-coded, like this:

OPENROWSET(BULK 'C:\picture.jpg', SINGLE_BLOB)

SK

Michal1979@.discussions.microsoft.com wrote:

> NNTP User

>

>

> Lot thanks for the repy,

>

> The article link is very interesting.

>

> I understand risks from using dynamic sql, but I'm not goiing to allow

> user call my stored procedure directly.

>

> All arguments of the stored procedure will be validated and constructed

> my my application.

>

> My real question is why can I use OPENROWSET function in stored

> procedure when I build the query

>

> using varchar variable and then execute it, although I can't do it

> direclty using OPENROWSET in

>

> stored procedure.

>

>

>

>

>

>|||

NNTP User,

Yes,I can not use directly OPENROWSET function in strored procedure, but I can use dynamic sql.

My problem is not how to store image using stored procedure (I can do it, however I don't like the way I do it)

but why can't I do it directly.

By the way I tried to use sp_executesql to run the code but it didn't work as well - so for now the only way

to store image or file using stored procedure is dynamic sql.

But I'm still wandering WHY? Is it a bug or something like that?

|||

As Steve indicated, you will have to specify the filename as a literal in the OPENROWSET call. Otherwise you will have to use dynamic SQL to form the entire statement and execute it. And with dynamic SQL you will have protect against SQL injection attacks. Optionally, you can do the following for a bulk import process:

1. Create temporary table to hold the user accounts

2. Use the new BulkCopy managed API to stream the user data from the client to server

3. Write SP to dump the rows from temporary table to the main table

For adding or modifying single values, you can just have a SP with image parameter and manipulate the data in the table. There is no need to use OPENROWSET which requires a file (creation of file by client, server having permissions to access file, etc) among other things and parameterization is not straight-forward.

|||

Umachandar Jayachandran - MS,

I'd like to know if there is other than OPENROWSET form of loading images or binary files into MSSQL,

I could missed it in documentation. It is sure that there will be no possibility to sql injection using dynamic sql

in my case.

Probably I should mentioned it before, the problem I described is not critical for me, becouse I found solution.

The solution may not be ideal (especialy for me) but it still works. Anyway probably I'll use C# and ADO.NET to

perform put and get image from database.

Maybe you know why the only way to use OPENROWSET in stored procedure is to use dynamic sql.

Thanks

MSsQL2005; OPENROWSET, BLOB/IMAGE and STORED PROCEDURE problems

All,

I work with Microsoft SQL Server 2005 on windows XP professional.
I'd like to create stored procdure to add image to my database (jpg file).
I managed to do it using VARCHAR variable in stored procedure
and then using EXEC, but it don't work directly.

My Table definiton:
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Login] [char](10),
[Password] [char](20),
[Avatar] [image] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My working solution using stored procedure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
SET @.Query = 'INSERT INTO USERS ' + CHAR(13)
+ 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)
+ '''' + @.Password + ''' AS Password,' + CHAR(13)
+ '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)'
EXECUTE (@.Query)
SET @.UserID = @.@.IDENTITY
END

I'd like to use statement in the stored procdure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
DECLARE
@.Query AS VARCHAR(MAX)

SET @.AvatarFileLocation = 'C:\hitman1.jpg'
INSERT INTO USERS
SELECT @.Login AS Login,
@.Password AS Password,
(SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)

SET @.UserID = @.@.IDENTITY

END

It generates error:
Incorrect syntax near '@.AvatarFileLocation'.

My question is:
Why it does not work and how to write the stored procedure code to run this code without errors.

Thanks for any reply

You can't use a variable inside OPENROWSET.

What you are doing, in any case, IS VERY DANGEROUS. There

are many ways in which dynamic SQL is vulnerable to SQL

injection. Please read about it, so that you don't lose

everything you have when a malicious user joins your

site/forum with a password like

O',0x; delete from USERS where Password <> 'O';return 0;--

You might start reading here:

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

Steve Kass

Drew University

Michal1979@.discussions.microsoft.com wrote:

> All,

>

> I work with Microsoft SQL Server 2005 on windows XP professional.

> I'd like to create stored procdure to add image to my database (jpg

> file).

> I managed to do it using VARCHAR variable in stored procedure

> and then using EXEC, but it don't work directly.

>

> My Table definiton:

> CREATE TABLE [dbo].[Users](

> [UserID] [int] IDENTITY(1,1) NOT NULL,

> [Login] [char](10),

> [Password] [char](20),

> [Avatar] [image] NULL,

> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

> (

> [UserID] ASC

> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

>

> My working solution using stored procedure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> SET @.Query = 'INSERT INTO USERS ' + CHAR(13)

> + 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)

> + '''' + @.Password + ''' AS Password,' + CHAR(13)

> + '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''',

> SINGLE_BLOB) AS OBRAZEK)'

> EXECUTE (@.Query)

> SET @.UserID = @.@.IDENTITY

> END

>

> I'd like to use statement in the stored procdure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> DECLARE

> @.Query AS VARCHAR(MAX)

>

> SET @.AvatarFileLocation = 'C:\hitman1.jpg'

> INSERT INTO USERS

> SELECT @.Login AS Login,

> @.Password AS Password,

> (SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS

> OBRAZEK)

>

>

> SET @.UserID = @.@.IDENTITY

>

> END

>

>

> It generates error:

> Incorrect syntax near '@.AvatarFileLocation'.

>

> My question is:

> Why it does not work and how to write the stored procedure code to run

> this code without errors.

>

> Thanks for any reply

>

>

>

>|||

NNTP User

Lot thanks for the repy,

The article link is very interesting.

I understand risks from using dynamic sql, but I'm not goiing to allow user call my stored procedure directly.

All arguments of the stored procedure will be validated and constructed my my application.

My real question is why can I use OPENROWSET function in stored procedure when I build the query

using varchar variable and then execute it, although I can't do it direclty using OPENROWSET in

stored procedure.

|||You cannot use a variable like this:

OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB)

The file name must be hard-coded, like this:

OPENROWSET(BULK 'C:\picture.jpg', SINGLE_BLOB)

SK

Michal1979@.discussions.microsoft.com wrote:

> NNTP User

>

>

> Lot thanks for the repy,

>

> The article link is very interesting.

>

> I understand risks from using dynamic sql, but I'm not goiing to allow

> user call my stored procedure directly.

>

> All arguments of the stored procedure will be validated and constructed

> my my application.

>

> My real question is why can I use OPENROWSET function in stored

> procedure when I build the query

>

> using varchar variable and then execute it, although I can't do it

> direclty using OPENROWSET in

>

> stored procedure.

>

>

>

>

>

>|||

NNTP User,

Yes,I can not use directly OPENROWSET function in strored procedure, but I can use dynamic sql.

My problem is not how to store image using stored procedure (I can do it, however I don't like the way I do it)

but why can't I do it directly.

By the way I tried to use sp_executesql to run the code but it didn't work as well - so for now the only way

to store image or file using stored procedure is dynamic sql.

But I'm still wandering WHY? Is it a bug or something like that?

|||

As Steve indicated, you will have to specify the filename as a literal in the OPENROWSET call. Otherwise you will have to use dynamic SQL to form the entire statement and execute it. And with dynamic SQL you will have protect against SQL injection attacks. Optionally, you can do the following for a bulk import process:

1. Create temporary table to hold the user accounts

2. Use the new BulkCopy managed API to stream the user data from the client to server

3. Write SP to dump the rows from temporary table to the main table

For adding or modifying single values, you can just have a SP with image parameter and manipulate the data in the table. There is no need to use OPENROWSET which requires a file (creation of file by client, server having permissions to access file, etc) among other things and parameterization is not straight-forward.

|||

Umachandar Jayachandran - MS,

I'd like to know if there is other than OPENROWSET form of loading images or binary files into MSSQL,

I could missed it in documentation. It is sure that there will be no possibility to sql injection using dynamic sql

in my case.

Probably I should mentioned it before, the problem I described is not critical for me, becouse I found solution.

The solution may not be ideal (especialy for me) but it still works. Anyway probably I'll use C# and ADO.NET to

perform put and get image from database.

Maybe you know why the only way to use OPENROWSET in stored procedure is to use dynamic sql.

Thanks

MSSQL Stored procedure Query using SELECT TOP

Is there a way to do a SELECT TOP # using a variable for the #?

In other words I'm doing a SELECT TOP 50* FROM DATATABLE

If I pass an @.value for the number

SELECT TOP @.value* FROM DATATABLE doesn't work

I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.

Thanks in advance.

I believe that is possible in 2005 and not in 2000. You can however use SET ROWCOUNT in 2000.

DECLARE @.tintSET @.t = 10SET ROWCOUNT @.tSELECT *FROM YourTableSET ROWCOUNT 0

|||

In 2005, the following works:

DECLARE @.t int
SET @.t = 20
SELECT TOP(@.t) * FROM yourTable