Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

AS

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 Parameter

I've to create an instance reference to my custom assembly by constructor
method.
It works well with a Code into the report .rdl like this:
---
<Code>
Public Obj As MyClass
Protected Overrides Sub OnInit()
dim MyArg as String = 4
Obj = new MyClass(MyArg)
End Sub
</Code>
--
But i need to pass a real parameter so:
Obj = new MyClass(Parameters!MyPar.Value)
it don't work and break with the error
BC30469 "The reference to a member not shared needs a reference to an object"
(I translate this from the italian version.... sorry for my bad english!!!
How can i do'
Thanks a lot.Try using this reference.
Report.Parameters!MyPar.Value
that should work in the Code.section
--
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
"Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> I've to create an instance reference to my custom assembly by constructor
> method.
> It works well with a Code into the report .rdl like this:
> ---
> <Code>
> Public Obj As MyClass
> Protected Overrides Sub OnInit()
> dim MyArg as String = 4
> Obj = new MyClass(MyArg)
> End Sub
> </Code>
> --
> But i need to pass a real parameter so:
> Obj = new MyClass(Parameters!MyPar.Value)
> it don't work and break with the error
> BC30469 "The reference to a member not shared needs a reference to an
> object"
> (I translate this from the italian version.... sorry for my bad
> english!!!
> How can i do'
> Thanks a lot.|||This not works, but the error is changed with a message box:
" Unable to load the assembly expressions. The expression refers to a
nonexistent parameter in the parameters's collection.".
This is not true, becouse the parameter exists.
Can you help me again?
"Wayne Snyder" wrote:
> Try using this reference.
> Report.Parameters!MyPar.Value
> that should work in the Code.section
> --
> 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
> "Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
> news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> > I've to create an instance reference to my custom assembly by constructor
> > method.
> > It works well with a Code into the report .rdl like this:
> > ---
> > <Code>
> > Public Obj As MyClass
> > Protected Overrides Sub OnInit()
> > dim MyArg as String = 4
> > Obj = new MyClass(MyArg)
> > End Sub
> > </Code>
> > --
> > But i need to pass a real parameter so:
> > Obj = new MyClass(Parameters!MyPar.Value)
> > it don't work and break with the error
> > BC30469 "The reference to a member not shared needs a reference to an
> > object"
> > (I translate this from the italian version.... sorry for my bad
> > english!!!
> > How can i do'
> > Thanks a lot.
>
>

Passing Multiple Parameters

hi, i am Create a search page"VB 6" which takes parameters as passing values from 6 different textboxes

on click ok the result should get in a Grid

User May be Enter 1 or 2 or 3 or all 6 values with different Combination

Kindly Help me how can I control Querey

Regards

Fakhruddin

Well, there are lots of variations for that.

Can you post some information about your table(s) the query you're using?

|||

Here you have a couple of outstanding articles about that theme.

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

The Curse and Blessings of Dynamic SQL

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

AMB

|||yet another split problem.. here's another resource|||

Why dynamic SQL here, You can do it straight forward,

Have a 6 Parameters in the SP.

Pass the each value from the textbox (if user doesn't enter any value for the text box, pass NULL).

Then use the following code on your sp,

Code Snippet

Create proc SearchData

(

@.Param1 as Varchar(100) = null,

@.Param2 as Varchar(100) = null,

@.Param3 as Varchar(100) = null,

@.Param4 as Varchar(100) = null,

@.Param5 as Varchar(100) = null,

@.Param6 as Varchar(100) = null

)

As

Begin

Select

..

..

From

Tables ...

Where

(@.Param1 is NULL or Column1 = @.Param1)

And (@.Param2 is NULL or Column2 = @.Param2)

And (@.Param3 is NULL or Column3 = @.Param3)

And (@.Param4 is NULL or Column4 = @.Param4)

And (@.Param5 is NULL or Column5 = @.Param5)

And (@.Param6 is NULL or Column6 = @.Param6)

End

|||

Hi Manivannan.D.Sekaran,

I wish it is as simple as that, but not, it is not. Depend on the indexes existing for the table in question, the kind of expression used in the "where" clause and the selectivity of the indexes based on the parameters value, the approach you use will be very important regarding to performance. The expression you used, does not yield good performance when there is and index by that column with a high selectivity. See the execution plan of the following examples:

Code Snippet

use northwind

go

create procedure dbo.p1;1

@.orderid int = null

as

set nocount on

select orderid, customerid, orderdate

from dbo.orders

where orderid = @.orderid or @.orderid is null

go

create procedure dbo.p1;2

@.orderid int = null

as

set nocount on

if @.orderid is null

select orderid, customerid, orderdate

from dbo.orders

else

select orderid, customerid, orderdate

from dbo.orders

where orderid = @.orderid

go

set showplan_text off
go

exec dbo.p1;1 10250

go

exec dbo.p1;2 10250

go

set showplan_text off

go

drop procedure dbo.p1

go

You will find a deep analysis in the articles, written by Erland Sommarskog, I posted in my previous message.

AMB

|||

Mani,

I confess that I often use the method you posted (or at least a variation using coalesce( @.Param, Column). I limit my usage to known 'smallish' tables where I am satisfied there is little probability of index usage.

The warning that Erland makes in his research/articles is that when the tables are large, the query plan may be totally wrong for various combinations of supplied parameters when using this approach.

So yours is a decent suggestion, yet one that should be presented with a caveat about the potential issues.

Hunchie's correct in providing example code so that all reading this thread can examine the issues for themselves. If you add the following code to Hunchie's example, you will see that the coalesce() option has a more efficient execution plan than the

(@.Param1 is NULL or Column1 = @.Param1)

option. However, the 'else' option produces the 'best' plan of the three methods.

All that said, sometimes the simplicity of the coalesce option with small data sets makes it a choice to consider.

Code Snippet

create procedure dbo.p1;3
@.orderid int = null
as
set nocount on
select orderid, customerid, orderdate
from dbo.orders
where orderid = coalesce( @.orderid, orderid )
go

exec dbo.p1;3 10250
go

|||It is definitely important that you give more information. If the text boxes are not related, there is one solution, and if they are an array, there is another. Also, if there are millions of rows, you may have to do some other things (like using dynamic SQL or multiple stored procedures.)

Passing Job to a JobStep

Back in the day of COM and DMO. You could create a COM component, create an SQL job, and using an 'AcitveX Script' job step pass the Job to the component.

The component could then lookup the job schedule using DMO to figure out how long it should run.

Now in the days of SMO and CLR. I want to pass the Job to a CLR Stored Procedure as part of a Transact SQL step... (without hard coding the JobId in the script)

any help is appreciated...

rich

Well, I guess I'll have to do some extra work.

I will automate the job creation and pass put the JobId into the Trasact SQL.

The reason I need this is that the sproc is long running and manages it's own schedule. After it executes it determines the next runtime and updates the schedule on the job

rich

sql

Passing in Date Parameters

I have a stored procedure that accepts a date value.

Create Procedure spTest as
@.DateFrom DateTime,
@.DateTo DateTime
/* Procedure Logic Not Shown */
GO

I'm trying to pass in a date generated from Functions.

EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'

When I run the Stored Procedure I get an "error near Month" but when I hard code in a date things work fine. i.e. '1/1/2003'. Any ideas?From the code taht you presented to us, I see two problems:

1)
Create Procedure spTest as
@.DateFrom DateTime,
@.DateTo DateTime
/* Procedure Logic Not Shown */
GO

should be like this:
Create Procedure spTest @.DateFrom DateTime,
@.DateTo DateTime as
/* Procedure Logic Not Shown */
GO

but this one I think it's just a type error when you posted your question, because you said that the sp worked when you passed the parameters hardcoded. The sp would never work (and never compile) in the form mentioned in your post

2)
Instead of:
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'
I would use:
EXECUTE spTest '' & Month(GetDate()) & '/1/2003', '2/2/2003'

Good luck!
ionut calin|||EXECUTE spTest '' & Month(GetDate()) & '/1/2003', '2/2/2003'

I paired up the quotes and the above line doesn't seem to work. Also tried:

EXECUTE spTest '' & Month(GetDate()) & '/1/2003''', '2/2/2003'
EXECUTE spTest Month(GetDate()) & '/1/2003', '2/2/2003'
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'

Just in case it was a data conversion issue I tried the following as well.

EXECUTE spTest CONVERT(VARCHAR(2), Month(GetDate)) + '/1/2003', '9/1/2003'

passing in a value to use as a column 'as name' in a stored proc

Hi,
I want to hand into a store procude the column name to use in the returned
result set...
create proc sample
@.colName as nvarcher(20)
as
select col1 as @.colname, col2 from table1..
But this produces an error... saying incorrect syntax near @.colname
is there a way to do want i am trying to do here?
ThanksThe curse and blessings of dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Martin C K Poon
Senior Analyst Programmer
====================================
"Aussie Rules" <AussieRules@.nospam.nospam> bl
news:uvQ1pgOjGHA.3572@.TK2MSFTNGP04.phx.gbl g...
> Hi,
> I want to hand into a store procude the column name to use in the returned
> result set...
> create proc sample
> @.colName as nvarcher(20)
> as
> select col1 as @.colname, col2 from table1..
> But this produces an error... saying incorrect syntax near @.colname
> is there a way to do want i am trying to do here?
> Thanks
>
>
>|||Thanks for Martin's informative inputs.
Hi Aussie,
I agree with Martin that you would need to consider using the dynamic SQL
execution. And in SQL Server the "exec" or "execute" keyword to execute
dynamic generated T-SQL statements:
#EXECUTE
http://msdn.microsoft.com/library/e...asp?frame=true
BTW, dynamic sql will have additional performance overhead comparing to
static T-SQL execution. Also, when we use string concatenate to generate
dynamic dynamic T-SQL statement, we would also take care of SQL injection
issue:
#SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Hope this also helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Aussie,
Have you got any progress or new ideas on this issue or does our replies
help you some? If there is still anything we can help, please feel free to
post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Passing authentication from PHP to reporting services

Hello,

My boss wants me to create a front end webpage for our Reporting
Services reports that customers can log onto through a webform. I'm a
PHP programmer, so I'd rather do this site and the authentication in
PHP than learn .NET. The main problem I see is finding a way to pass
the authentication to Reporting Services so that when a user runs a
report, they are not asked to enter their password a second time.

I don't even know where to start researching this problem... I've asked
in PHP forums and they passed me to you guys. Any ideas?

AndrewIf no one knows the answer to this, does anyone know a better place
that I could ask this question?|||andrewdmason@.gmail.com (andrewdmason@.gmail.com) writes:
> If no one knows the answer to this, does anyone know a better place
> that I could ask this question?

That would be microsoft.public.sqlserver.repotingsvcs.

If your local newserver does not carry it, it's available at
msnews.microsoft.com.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Setup anonymous authentication (instead of NT Challenge Response, the
default) for MS Reporting services and diallow all connections besides
the PHP server and then you don't have to worry about authentication at
all.

Erland Sommarskog wrote:
> andrewdmason@.gmail.com (andrewdmason@.gmail.com) writes:
> > If no one knows the answer to this, does anyone know a better place
> > that I could ask this question?
> That would be microsoft.public.sqlserver.repotingsvcs.
> If your local newserver does not carry it, it's available at
> msnews.microsoft.com.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

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 variable to the UPDATE Command

Hello everybody,

I have a problem in passing a variable into the Update command. This is my stored procedure.

CREATE PROCEDURE UpdateTable @.tsID INT, @.UpdateDesc varchar(1000)
AS

DECLARE @.TableNumber INT
DECLARE @.TableName NVARCHAR(100)
DECLARE @.SQL VARCHAR(100)

/* I have to get the name of the table to be updated by first getting the TableId
( an INT field) from a table TS_SUBTASKS and with that the table name from another table TS_TABLES */

SELECT @.TableNumber = TS_SUBTABLEID FROM TS_SUBTASKS
WHERE TS_SUBITEMID = @.tsID

SELECT @.TableName = TS_DBNAME FROM TS_TABLES
WHERE TS_ID = @.TableNumber

/* Which the Table name in the variable @.TableName i have to set one of its
fields, TS_DESCRIPTION to the value passed into the procedure
(@.UpdateDesc) */

SET @.SQL = 'update ' + @.TableName+ 'SET TS_DESCRIPTION = ' + @.UpdateDesc + ' WHERE TS_ID = ' + @.tsID

EXEC(@.SQL)

GO

--But i get this error

Syntax error converting the varchar value 'update USR_HUMAN_RESOURCESSET TS_DESCRIPTION = newdescription WHERE TS_ID = ' to a column of data type int.

It would be very helpful if somebody could throw light on this.
Thanks,
Krishna Murthy.

You should really rethink your approach of using dynamic SQL and this type of design. It requires lot of work in terms of giving permissions to users directly to update the table and coding the dynamic SQL. It is best to use static SQL wherever possible since it is more secure and easier to manage. The errors you are seeing are a result of incorrecly formed UPDATE statement. I believe it is missing some quotes. You should also consider using sp_executesql since that is easier to parameterize like below and you can avoid some of the SQL injection problems with using EXEC incorrectly:

DECLARE @.SQL nvarchar(1000)
SET @.SQL = N'update ' + QUOTENAME(@.TableName) +
N'SET TS_DESCRIPTION = @.Descr WHERE TS_ID = @.ID'
exec sp_executesql @.SQL, N'@.Descr varchar(1000), @.ID int', @.Descr = @.UpdateDesc, @.ID = @.tsID
|||Thank you Mr. Jayachandran, your correction has helped me a great deal. And coming to your suggestion of not taking this approach, I could not find any other way of doing the same. I shall again be very thankful to you if you can suggest a better way of doing this.

Thanks,
Krishna Murthy.|||I meant that you should avoid writing code that relies on dynamic schema elements. They are difficult to manage and code. In your case, you may want to consider generating some of the SP code during the creation at the time of definition of the row that represents a table. This approach also depends on your application and requirements.

Passing a parameter as list in a Stored procedure

Hi All
How can i create a stored procedure which can pass a parameter as a list.
Below is sample sp:
create procedure spxx (@.@.param1 varchar(100))
as
select * from tb1 where col1 = @.@.param1
When executing i like would like to be able to do this:
1. exec spxx 'John'
0r
2. exec spxx 'John', 'Mary', 'Susan'
Thank you in advance.http://www.aspfaq.com/2248
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:B4089DB0-3CA6-432E-BCC2-108BB5502D74@.microsoft.com...
> Hi All
> How can i create a stored procedure which can pass a parameter as a list.
> Below is sample sp:
> create procedure spxx (@.@.param1 varchar(100))
> as
> select * from tb1 where col1 = @.@.param1
> When executing i like would like to be able to do this:
> 1. exec spxx 'John'
> 0r
> 2. exec spxx 'John', 'Mary', 'Susan'
> Thank you in advance.
>|||You pass it as a string and split it inside the Stored Proc.
Check this out...
http://www.sommarskog.se/arrays-in-sql.html
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||MittyKom wrote:
> Hi All
> How can i create a stored procedure which can pass a parameter as a list.
> Below is sample sp:
> create procedure spxx (@.@.param1 varchar(100))
> as
> select * from tb1 where col1 = @.@.param1
> When executing i like would like to be able to do this:
> 1. exec spxx 'John'
> 0r
> 2. exec spxx 'John', 'Mary', 'Susan'
> Thank you in advance.
>
One method:
http://www.realsqlguy.com/?p=9|||>> How can I create a stored procedure which can pass a parameter as a list.
<<
This is a common newbie error. Which you did not bother to Google
before posting, did you? It immediately tells us that:
1) You do not know what a compiled program is, but expect SQL to be an
intrpreter.
2) You do not know what a parameter or understand the concept of a
scalar value.
3) You do not know that a table is the only data structure in SQL --
there are no lists!
The kludge you get in a newsgroup is dynamic SQL, with all the problems
that come with it. Hey, but a kludge is so much easier and faster than
an education, or even a Google search beforee posting!
We can also give you some pure SQL that split up a string. But that is
not the point; this is a bad design and poor programming. If you
really give a damn about doing it right, Please post DDL, so that
people do not have to guess what the keys, constraints, Declarative
Referential Integrity, data types, etc. in your schema are. Sample data
is also a good idea, along with clear specifications.|||And there is this one:
http://www.realsqlguy.com/?p=9
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:A220E647-6408-4428-A397-49E268158664@.microsoft.com...
> You pass it as a string and split it inside the Stored Proc.
> Check this out...
> http://www.sommarskog.se/arrays-in-sql.html
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Thank you all. Is there a way of doing this without using a function?
"Omnibuzz" wrote:

> You pass it as a string and split it inside the Stored Proc.
> Check this out...
> http://www.sommarskog.se/arrays-in-sql.html
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||something like this?
if exists(
Select 1 from TKCalls.dbo.tblCalls
where DATEDIFF(mi, StartedTime, GETDATE()) <=30
AND left(cast(Icent_Num as varchar(20)),6) = ('962472')
)
SET @.COUNT_CALLS_REC_1 = 1
else
SET @.COUNT_CALLS_REC_1 = 0
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This answer tells us
1) You have no real industrial programming experience
2) You do not understand how SQL is being used out in the field
3) You are ignorant, condescending and arrogant
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151689463.194144.106580@.d56g2000cwd.googlegroups.com...
> This is a common newbie error. Which you did not bother to Google
> before posting, did you? It immediately tells us that:
> 1) You do not know what a compiled program is, but expect SQL to be an
> intrpreter.
> 2) You do not know what a parameter or understand the concept of a
> scalar value.
> 3) You do not know that a table is the only data structure in SQL --
> there are no lists!
> The kludge you get in a newsgroup is dynamic SQL, with all the problems
> that come with it. Hey, but a kludge is so much easier and faster than
> an education, or even a Google search beforee posting!
> We can also give you some pure SQL that split up a string. But that is
> not the point; this is a bad design and poor programming. If you
> really give a damn about doing it right, Please post DDL, so that
> people do not have to guess what the keys, constraints, Declarative
> Referential Integrity, data types, etc. in your schema are. Sample data
> is also a good idea, along with clear specifications.
>|||I have tried to ignore your reply, but it's still bothering me. I have gone
through a few of your replies in this newsgroup and they are not helpful.You
are not being forced to help us here. So please if you are willing to help,
do so without all these stupid comments. Also if you are a programmer, it
does not mean that everyone is a programmer in this newsgroup. Cheers.
"--CELKO--" wrote:

> This is a common newbie error. Which you did not bother to Google
> before posting, did you? It immediately tells us that:
> 1) You do not know what a compiled program is, but expect SQL to be an
> intrpreter.
> 2) You do not know what a parameter or understand the concept of a
> scalar value.
> 3) You do not know that a table is the only data structure in SQL --
> there are no lists!
> The kludge you get in a newsgroup is dynamic SQL, with all the problems
> that come with it. Hey, but a kludge is so much easier and faster than
> an education, or even a Google search beforee posting!
> We can also give you some pure SQL that split up a string. But that is
> not the point; this is a bad design and poor programming. If you
> really give a damn about doing it right, Please post DDL, so that
> people do not have to guess what the keys, constraints, Declarative
> Referential Integrity, data types, etc. in your schema are. Sample data
> is also a good idea, along with clear specifications.
>

passing a new query to a report

hi all,

ok, here is my problem. my employer has tasked me to create fairly complex program.. and with it he wants a reporting system. now, i have been given permission to use VB Express, SQL Server 2005 Express with Advanced Features, and the SQL Server Express Toolkit for my application. my employer would prefer not to have to spend the money on visual studio professional (although, in truth it would make my life so much easier as reports are integrated into the IDE, via crystalreports), so i HAVE to do it the way of viewing all reports through ie.

now (after much toil) i have finally figured out how to display the reports in an internet explorer window (which i can also run from my application), and the report displays no problem! when i finally managed this i was through the bloody moon. now, what i would like to do is one of two things...

1) display a report based on a dynamic query from my vb.net form, so i can filter the results how i want

or

2) simply define a static query from within the report, create a couple of parameters in the report, and supply the parameters to the report from the vb.net form, via maybe the url of the report?

i have been thinking on this for a while and i have not been able to come up with anything, im hoping someone here will have gone through a similar problem and will be able to help me out

if anyone could provide advice, tutorials, links on how to go about accomplishing this i would be eternally grateful!!!

regards

adam

after quite a bit of resarch i found it is in the MSDN library, you just have to search sql server reporting services.

this only allows passing of parameters... but if anyone knows how to pass a completely new query it would be greatly appreciated!

http://msdn2.microsoft.com/de-de/library/ms153586.aspx

hope this helps somebody!

regards

adam

Passing a dynamic table name to a stored procedure

Is this possible?

I know this doesn't work but I'll post it anyways so you can see what I'm trying to accomplish.

CREATE PROCEDURE GetStuff
 @.something int,
 @.tablename varchar(50)
AS
 SELECT * FROM @.tablename WHERE something = @.something

Like I say, I know it doesn't work...but does anyone know how to accomplish something like this?there are some limitations to this code but it works for most parts :
using NOrthwind database :

CREATE PROC sp_distinctcount
 @.table_name AS SYSNAME,
 @.cat AS int
AS
DECLARE @.sql AS nvarchar(1000)
SET @.sql = N'SELECT *'+ N' FROM ['
 + @.table_name + N'] where categoryid=' + convert(varchar(10),@.cat)
EXEC sp_executesql @.stmt = @.sql
GO

from QA :

EXEC northwind..sp_distinctcount
 @.table_name='categories',@.cat=1

hth

Passing @UserName into Stored Procedure

I have successfully Create a Site, Inserting Updating andDeleting information in my DB all with Stored Procedures, But I need theability to pass their username into my Stored Procedures. How and where do Icode this in my ASPX file?

My SP would be something like this

Create procedure test

@.UserNamevarchar(50)

As

Select *

From table

Where username= @.username

All of thedata is tied to the user in one way or another but I do not know what code toput in my page?

If you can get correct username in your application, why not use SqlCommand?

using(SqlConnection conn= new SqlConnection(@."Data Source=.\iori2000;Integrated Security=SSPI;Database=master"))
{
SqlCommand cmd= new SqlCommand("test",conn);
conn.Open();
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.username", username);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);

//add your code

}

|||

I do not know that I'm following.

Here is the Code I would like to put pull the username.

<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1" Style='position: relative'>
<Columns>
<asp:BoundField DataField="Task_Name" HeaderText="Task_Name" SortExpression="Task_Name" />
<asp:BoundField DataField="project_name" HeaderText="project_name" SortExpression="project_name" />
<asp:BoundField DataField="priority_desc" HeaderText="priority_desc" SortExpression="priority_desc" />
<asp:BoundField DataField="Assigned_to" HeaderText="Assigned_to" SortExpression="Assigned_to" />
<asp:BoundField DataField="status" HeaderText="status" SortExpression="status" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:WebAppConnectionString %>"
SelectCommand="task_summary_info" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</asp:Content>

Pass XML Data to a Stored Procedure

Does anyone know how to pass a XML File to a MS SQL 2005 Stored Procedure (INSERT/UPDATE), and how to create the stored procedure so it will accept the XML values using VB 2005.

Here is an example of the XML File.

Code:
<MYROOT>
<TableName>
<Field1>String</Field1>
<Field2>String</Field2>
<Field3>String</Field3>
</TableName>
</MYROOT>

Thank you.
bty The following link provided by Microsoft does not work in VB 2005, I have tried that

http://support.microsoft.com/default.aspx?scid=kb;en-us;555266

You can do this using XQuery to strip the values and pass them to the stored proc. You can view a good article on using XQuery here:

http://www.15seconds.com/issue/050803.htm

***

Download this free script (WSP Snapshot 1.0) to take snapshot sample of your web server(s) from anywhere/anytime using a browser. View the stats (cpu and disk stress, available memory, requests queued, request wait time and more) on a cellphone or PDA also. http://www.ifusionsoft.com

|||

Also check out Sushil's weblog on this ->

http://blogs.msdn.com/sushilc/archive/2004/08/03/207162.aspx

This explains the basics of sending XML to SQL 2005 as a parameter.

Pass XML between SPROCs possible in SQL 2000? 2005?

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

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

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

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

Pass variable number of parameters to a stored proc

Is it possible to create a stored proc that allows the passing of a variable
number of parameters? I'm creating a stored proc to populate a Suggested PO
form. It may require filtering on one or more vendor IDs (string data). I've
looked through BOL but nothing is jumping out at me. I also was looking at
the string manipulation functions for parsing a single variable but it looks
like it would be ugly. TIA!Hi,
You will have to use dynamic SQL inside the procedure to parse the parameter
variable which hold one or more input values.
See the below URL for the various usage of dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
SQL Server MVP
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:%23RizK7jxGHA.480@.TK2MSFTNGP06.phx.gbl...
> Is it possible to create a stored proc that allows the passing of a
> variable
> number of parameters? I'm creating a stored proc to populate a Suggested
> PO
> form. It may require filtering on one or more vendor IDs (string data).
> I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it
> looks
> like it would be ugly. TIA!
>
>|||Ron Hinds wrote:
> Is it possible to create a stored proc that allows the passing of a variab
le
> number of parameters? I'm creating a stored proc to populate a Suggested P
O
> form. It may require filtering on one or more vendor IDs (string data). I'
ve
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it loo
ks
> like it would be ugly. TIA!
>
>
Here is one approach:
CREATE PROCEDURE MyProc
@.Var1 INT = NULL,
@.Var2 INT = NULL
AS
SELECT Field1, Field2
FROM Table
WHERE
((@.Var1 IS NULL) OR (Field3 = @.Var1))
AND
((@.Var2 IS NULL) OR (Field4 = @.Var2))
The optimizer is smart enough to realize that if the left side of the OR
is true, there is no need to evaluate the right side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Pass variable number of parameters to a stored proc

Is it possible to create a stored proc that allows the passing of a variable
number of parameters? I'm creating a stored proc to populate a Suggested PO
form. It may require filtering on one or more vendor IDs (string data). I've
looked through BOL but nothing is jumping out at me. I also was looking at
the string manipulation functions for parsing a single variable but it looks
like it would be ugly. TIA!Hi,
You will have to use dynamic SQL inside the procedure to parse the parameter
variable which hold one or more input values.
See the below URL for the various usage of dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
SQL Server MVP
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23RizK7jxGHA.480@.TK2MSFTNGP06.phx.gbl...
> Is it possible to create a stored proc that allows the passing of a
> variable
> number of parameters? I'm creating a stored proc to populate a Suggested
> PO
> form. It may require filtering on one or more vendor IDs (string data).
> I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it
> looks
> like it would be ugly. TIA!
>
>|||Ron Hinds wrote:
> Is it possible to create a stored proc that allows the passing of a variable
> number of parameters? I'm creating a stored proc to populate a Suggested PO
> form. It may require filtering on one or more vendor IDs (string data). I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it looks
> like it would be ugly. TIA!
>
>
Here is one approach:
CREATE PROCEDURE MyProc
@.Var1 INT = NULL,
@.Var2 INT = NULL
AS
SELECT Field1, Field2
FROM Table
WHERE
((@.Var1 IS NULL) OR (Field3 = @.Var1))
AND
((@.Var2 IS NULL) OR (Field4 = @.Var2))
The optimizer is smart enough to realize that if the left side of the OR
is true, there is no need to evaluate the right side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Pass text (or, best, ntext) type value to an OLE Automation function call

Hello!
Is that possible to create a user-defined function, that would take value of
type text, and pass it to the OLE Automation function call?
The SP code I use to do OLE Aut. call, is as follows:
DECLARE @.object int
DECLARE @.hr int
DECLARE @.property nvarchar(255)
DECLARE @.src nvarchar(255), @.desc nvarchar(255)
DECLARE @.return nvarchar(255)
EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
PRINT @.return
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
****
I have currently working version, that uses char type, but I'd prefer to use
text or even ntext type. It seems like, that if it is not possible to pass
the text-type value to the OLE function call, I will be furced to pass a
record ID instead, and read the text value within the external code from the
same database, what actually sounds like risky way.
Thanks,
PavilsThere is no way to declare a local n/text variable. So, the answer is 'no'.
-oj
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:ufTfn5KTFHA.548@.tk2msftngp13.phx.gbl...
> Hello!
> Is that possible to create a user-defined function, that would take value
> of type text, and pass it to the OLE Automation function call?
> The SP code I use to do OLE Aut. call, is as follows:
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.property nvarchar(255)
> DECLARE @.src nvarchar(255), @.desc nvarchar(255)
> DECLARE @.return nvarchar(255)
> EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> PRINT @.return
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> ****
> I have currently working version, that uses char type, but I'd prefer to
> use text or even ntext type. It seems like, that if it is not possible to
> pass the text-type value to the OLE function call, I will be furced to
> pass a record ID instead, and read the text value within the external code
> from the same database, what actually sounds like risky way.
> Thanks,
> Pavils
>

Pass record to user-defined function and create xml

Does anyone know if there is a way to pass a record from a select to a
user defined function in SQL Server 2000? The number and names of the
columns in the record will vary depending on the upload temp table
selected from... You should be able to see what I am trying to
accomplish bellow... If you have another idea, I'm open to
suggestions.
Example:
Declare @.CurrentFiscalYear smallint
Set @.CurrentFiscalYear = 2005
Create table ClaimEditLog(
TransactionId int,
XMLData varchar(7500),
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Create Table #tbClaimUploadData(
TransactionId int IDENTITY (1, 1) NOT NULL ,
FiscalYear smallint,
AmountTypeId int,
Amount money
)
Insert #tbClaimUploadData
Select 2005, 2, 556.98
Insert #tbClaimUploadData
Select 2006, 2, 56.90
Insert into ClaimEditLog
Select TransactionId,
dbo.UDF_ConvertRecordToXML(*),
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
>From #tbClaimUploadData
Where FiscalYear <> @.CurrentFiscalYear
-- UDF_ConvertRecordToXML - Convert record to XML
Select * from ClaimEditLog
--Output
2,
'<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalYea
r><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>',
'The Fiscal Year is incorrect.',
2006-05-31 06:41:32.527You could store the complete record and use a computed column to give a XML
representation of the output...
So your table definition would be this...
Create table ClaimEditLog(
TransactionId int,
FiscalYear smallint,
AmountTypeId int,
Amount money,
XMLData AS
'<XMLData><Record><TransactionId>'
+ CAST( TransactionId as varchar(20) )
+ '<FiscalYear>' + CAST( FiscalYear AS char(4) ) + '
etc...',
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Your insert would be...
insert claimeditlog (
TransactionId,
FiscalYear,
AmountTypeId,
Amount,
EditDesc,
LastUpdateId,
LastUpdate )
select TransactionId,
FiscalYear,
AmountTypeId,
Amount,
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
from #tbClaimUploadData
Select * from ClaimEditLog
And you'd get the XMLData output as text XML.
Make sense?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<porsch55@.yahoo.com> wrote in message
news:1149077706.631013.220560@.y43g2000cwc.googlegroups.com...
> Does anyone know if there is a way to pass a record from a select to a
> user defined function in SQL Server 2000? The number and names of the
> columns in the record will vary depending on the upload temp table
> selected from... You should be able to see what I am trying to
> accomplish bellow... If you have another idea, I'm open to
> suggestions.
> Example:
> Declare @.CurrentFiscalYear smallint
> Set @.CurrentFiscalYear = 2005
> Create table ClaimEditLog(
> TransactionId int,
> XMLData varchar(7500),
> EditDesc varchar(200),
> LastUpdateId varchar(20),
> LastUpdate datetime
> )
> Create Table #tbClaimUploadData(
> TransactionId int IDENTITY (1, 1) NOT NULL ,
> FiscalYear smallint,
> AmountTypeId int,
> Amount money
> )
> Insert #tbClaimUploadData
> Select 2005, 2, 556.98
> Insert #tbClaimUploadData
> Select 2006, 2, 56.90
> Insert into ClaimEditLog
> Select TransactionId,
> dbo.UDF_ConvertRecordToXML(*),
> 'The Fiscal Year is incorrect.',
> 'jporscha',
> GetDate()
> Where FiscalYear <> @.CurrentFiscalYear
> -- UDF_ConvertRecordToXML - Convert record to XML
> Select * from ClaimEditLog
> --Output
> 2,
> '<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalY
ear><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>'
,
> 'The Fiscal Year is incorrect.',
> 2006-05-31 06:41:32.527
>

Pass Date Range from VB to CR

Hi All,

i m Currently using VB,CR 9 and MS-Access

i have Create Cr report,

in my Vb form two text boxes Start date and end date,Simply i just want to display my report between two dates..

how can i do this..

AnyoneCan help me...

Thanx in Advance

Regards,
SabinaI am glad that you ask this question because I have the same problem and wanted to ask this to. I hope that someone can help us.

Greetings, Sjaaaf|||Create 2 global variables in module.bas
Public gstrFrom as String
Public gstrTo as String

Let say your form frmPrint.frm has textboxes txtFrom.text and txtTo.text
Put inside command button Print

Private Sub cmdPrint_Click()
gstrFrom = Trim(txtFrom.text)
gstrTo = Trim(txtTo.text)

Dim RptViewer As New frmRPrint 'frmRPrint.frm has a CRViewer object
RptViewer.Show
End Sub

Inside frmRPrint code section:

Option Explicit

Public Report As New ProdReport 'ProdReport.dsr is a Designer file

Private Sub Form_Load()
Dim adoc As ADODB.Command
Dim strSQL As String
Dim conn As ADODB.Connection

Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2

Screen.MousePointer = vbHourglass

Set adoc = New ADODB.Command
Set conn = New ADODB.Connection

conn.Open ConnString 'Connection String to MS Access Database
adoc.ActiveConnection = conn

strSQL = "SELECT ItemID, Quantity" & _
" FROM Production" & _
" WHERE ProdDate BETWEEN '" & gstrFrom & "' AND '" & gstrTo & "'"
adoc.CommandText = strSQL
adoc.CommandType = adCmdText

Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName

Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")

conn.Close
Set conn = Nothing

CRViewer1.ReportSource = Report

CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

Note:
You also can use
strSQL = "... WHERE ProdDate > '" & gstrFrom & "' AND ProdDate <'" & gstrTo & "'"
If your date is in date time format, concantenate the Todate with "11.59 pm" or "< gstrTo +1" so it will also include records on that day or else it will only display records until d/m/yy 12.00am|||hi

Thanx u very much..

If i get any error,than i will again ask u question.....again thnx

God with u always,

Regards,

Sabina|||hi

As u gave me Code of the passing Date range i did it

but when i use

Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName

Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")

this codding lines

it not show me database,AutoSetUnboundFieldSource crBMTName like these properties

my report is a .dsr file...if i have Crystal report (.rpt file)than wha can i do...?

tell me plz what wil i do ,where Database etc. properties not display ...othertings u sais it is fine

Thanx in Advance

Regards,

Sabina|||Hi..

Instead of doing that one, try this one

XSTART = txtFrom.text
XEND = txtEnd.txt

Dim APP As New CRAXDRT.Application
Dim REPORT As CRAXDRT.Report

REPORT = APP.OpenReport("<your path>\<your reportfilename>.rpt")
REPORT.RecordSelectionFormula = "{<Table.Datefield>} >= #" & XSTART & "# AND {<Table.Datefield>} <= #" & XEND & "#"

Don't forget to Add the Crystal Report ActiveX Designer Run Time Library to your COM References ^_^|||Try this...

Place 2 Formula Fields in yr report and in the editor write "Date".

Then write the below code in yr VB codings and then execute the report.
CrystalReport1.Formulas(0) = "fdate='" & Format(DTPicker1.Value, "MMMM yyyy") & "'"
CrystalReport1.Formulas(1) = "tdate='" & Format(DTPicker2.Value, "MMMM yyyy") & "'"

Revert, if u still require assistance..