Parameterized Expression: parameterized

Showing posts with label parameterized. Show all posts

Tuesday, March 20, 2012

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
Yahya
Yahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>
|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User
defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...een">
> Can I create parameterized view, in other words like stored procedures whe
re
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Monday, February 20, 2012

Parameterized where clause

Hello,
I have an add stored procedure in Yukon (would work in 2000 too), where I select the ID from the table to make sure that it doesn't already have the data. So it looks like:
create procedure ..
..
set transaction isolation level serializable
begin transaction
declare @.ID int
select @.ID = rowid from tblBusinessInformation where Name = @.Name and Rules = @.Rules
if ( @.ID is NULL )
begin
insert into tblBusinessInformation (..) values (@.Name, @.Rules)
end
commit transaction
The problem is the values could be:
Name Rules
NULL 'Test'
'Test' NULL
'Test' 'Test'
When one of the values was NULL, it would never select the ID, unless I changed it to "where Name is @.Name", and then it worked, because where Name is NULL, which is correct in SQL; so how do I allow for both; I can use the CLR, but would like to avoid rewriting the proc if possible, and I thought that was to work...
Thanks.perhaps you can use the CASE stmt. So what xactly is your condition..you want to allow NULLs in both columns or select an ID if either Name or Rules is NULL ? If you can state what conditions you are trying to match we can help you with the SQL.|||

Hello,
Well, what I need to do is ensure uniqueness in the table, for the name/rules pairing, so for example, the proc searches for a @.name value of "bob" and a @.rules value of "must be a bob", this must be unique in the table. It isn't the primary key; I use an identity value for the key, so I have three fields, the ID, name, and rules. Now the @.names can be null or the @.rules can be null, but not both.
The problem is, with this query:
declare @.ID int
select @.ID = rowid from tblBusinessInformation where Name = @.Name and Rules = @.Rules
if @.Name is null and @.Rules has a value, it doesn't find the ID; however, for Name, when I change "Name = @.Name" to "Name is @.Name", then I find that it works; however, I can't use is because whenever @.Name isn't null, then that causes a problem. I thought this wasn't supposed to be the case.
I can use the CLR, but I was trying to avoid it because of all the code necessary for a simpler statement. Any ideas how to avoid this? As an alternative, I can try putting in a blank space for when it is null and then use the ISNULL function, but I would like to use the NULL value.
Thanks for looking at this.

|||

I dont think I completely understood but I will take a guess. You could do something like :

SELECT
@.ID = rowid
FROM
tblBusinessInformation
WHERE
ISNULL(Name,'') = ISNULL(@.Name,'') AND ISNULL(Rules,'') = ISNULL(@.Rules,'')

Parameterized View or Parameterized Stored Procedure

Dear All,
i have a complicated query which needs a parameter. I find it too cumbersone
to write the query in vb - instead can i create a stored procedure and pass
the parameter from vb and the stored procedure returns a recorset fully
loaded.
is it possible to create a parameterised query like in Access. My example
works fine in ms access. Only now i am

whether to use a stored
procedure or a view?
i am just starting and am using sql server 7.0 and vb. PLS Help Me.
Thanks
Manish
Three Cheers to technet for the Help!Yes you most certainly can... In fact, (although it's not always adviseable)
,
you can do almost anything in a stored proc that you could do in code.
(although some things are a bit harder).
You can definitely pass parameters to a stored Proc.. They can be the
equivilent of byval, or if you want to be be to access, on the client, the
value as modified by the SP, you can pass them "by reference". SQL Code in
the Stored Proc can use the passed in parameters to control the execution of
the SQL. There are many examples in Books On Line (BOL). Available to you
if you have installed SQL Client tools om your development box.
"Manish Sawjiani" wrote:

> Dear All,
> i have a complicated query which needs a parameter. I find it too cumberso
ne
> to write the query in vb - instead can i create a stored procedure and pas
s
> the parameter from vb and the stored procedure returns a recorset fully
> loaded.
> is it possible to create a parameterised query like in Access. My example
> works fine in ms access. Only now i am

whether to use a stored
> procedure or a view?
> i am just starting and am using sql server 7.0 and vb. PLS Help Me.
> Thanks
> Manish
>
> --
> Three Cheers to technet for the Help!|||Thanks CBretana for your encouraging response and i will look up the books
online for help. Thanks again. What about a view? is view like a SP?
"CBretana" wrote:
> Yes you most certainly can... In fact, (although it's not always adviseabl
e),
> you can do almost anything in a stored proc that you could do in code.
> (although some things are a bit harder).
> You can definitely pass parameters to a stored Proc.. They can be the
> equivilent of byval, or if you want to be be to access, on the client, the
> value as modified by the SP, you can pass them "by reference". SQL Code
in
> the Stored Proc can use the passed in parameters to control the execution
of
> the SQL. There are many examples in Books On Line (BOL). Available to yo
u
> if you have installed SQL Client tools om your development box.
> "Manish Sawjiani" wrote:
>|||Adding to Charles' suggestion, views and stored procedures are intended for
different purposes, though in few cases, one could use a view instead of a
stored procedure and vice-versa. The primary purpose of a view is logical
data independence and data security.
There are no parameterized views in SQL Server. The general alternative
which is often suggested is a table valued UDF ( user-defined function )
which can be used inline. However UDFs are introduced only in SQL Server
2000 so you will have to use stored procedures instead, perhaps with
re-written constructs.
Anith

parameterized update sp_executesql

I am trying to limit network traffic by only sending fields that have actually changed to an update stored procedure. I am obviously not doing this correctly as I am rather new to this. This is a small version of my sproc that I am using to test the workings. I am getting 'Error converting data type nvarchar to int'. If you can help me get this easy query working, I would very much like your help to use the EXEC (sql) where sql is an sp_executesql statement because some of my update fields are much larger than 4000 nvarchar. The following statement was working when I was using the second version. If I am trying to update an integer foreign key field, shouldn't I try to use the parameters to send an integer value for that field, rather than an nvarchar? As you can see, I really need to get some not so basic info on this. I have done hours of research on the net and can't find some of these simple background principals. I also question whether I am saving enough network traffic to validate the cost in processing for building this query and executing it. Any help you can provide will be greatly appreciated.

Non working

CREATE PROCEDURE tspJobs_Updatetest
(
@.JobID int=0,
@.AddressID int=0,
@.AddressChg int=0,
@.CustomerID int=0,
@.CustomerChg int=0,
@.ContactID int=0,
@.ContactChg int=0,
@.PlanID int=0,
@.PlanChg int=0,

)
AS

DECLARE @.updateClause nvarchar(1000);
DECLARE @.whereClause nvarchar(500);
DECLARE @.sqlStatement nvarchar(4000);
DECLARE @.paramDefinition nvarchar(1000);
DECLARE @.valuesDefinition nvarchar(1500);

--Set Where Clause and parameter statement

IF @.JobID > 0
BEGIN
SET @.whereClause = ' WHERE job_id = @.update_job_id '
SET @.paramDefinition = N'@.update_job_id int '
SET @.valuesDefinition = N'@.update_job_id=@.JobID '

--Build Update Statement

SET @.updateClause = null

IF @.AddressChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_address_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_address_id = @.AddressID'
IF @.updateClause IS NULL
SET @.updateClause = N'address_id = @.update_address_id'
ELSE
SET @.updateClause = @.updateClause + N', address_id = @.update_address_id'
END

IF @.CustomerChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_customer_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_customer_id = @.CustomerID'
IF @.updateClause IS NULL
SET @.updateClause = N'customer_id = @.update_customer_id'
ELSE
SET @.updateClause = @.updateClause + N', customer_id = @.update_customer_id'
END

IF @.ContactChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_contact_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_contact_id = @.ContactID'
IF @.updateClause IS NULL
BEGIN
IF @.ContactID = 0
SET @.updateClause = N'contact_id = NULL'
ELSE
SET @.updateClause = N'contact_id = @.update_contact_id'
END
ELSE
BEGIN
IF @.ContactID = 0
SET @.updateClause = @.updateClause + N', contact_id = NULL'
ELSE
SET @.updateClause = @.updateClause + N', contact_id = @.update_contact_id'
END
END

IF @.PlanChg > 0
BEGIN
SET @.paramDefinition = @.paramDefinition + N', @.update_plan_id int'
SET @.valuesDefinition = @.valuesDefinition + N', @.update_plan_id = @.PlanID'
IF @.updateClause IS NULL
BEGIN
IF @.PlanID = 0
SET @.updateClause = N'plan_id = NULL'
ELSE
SET @.updateClause = N'plan_id = @.update_plan_id'
END
ELSE
BEGIN
IF @.PlanID = 0
SET @.updateClause = @.updateClause + N', plan_id = NULL'
ELSE
SET @.updateClause = @.updateClause + N', plan_id = @.update_plan_id'
END
END

-- Complete SQL statement

IF NOT @.updateClause IS NULL
BEGIN
SET @.sqlStatement = 'UPDATE [dbo].[Jobs] SET ' + @.updateClause + @.whereClause
EXEC sp_executesql @.sqlStatement,@.paramDefinition, @.valuesDefinition
END
END
GO

Working, but using nvarchar for integer foreign key field updates

CREATE PROCEDURE tspJobs_Update
(
@.JobID int=0,
@.AddressID int=0,
@.AddressChg int=0,
@.CustomerID int=0,
@.CustomerChg int=0,
@.ContactID int=0,
@.ContactChg int=0,
@.PlanID int=0,
@.PlanChg int=0

)
AS

DECLARE @.updateClause varchar(3000);
DECLARE @.whereClause varchar(1000);
DECLARE @.sqlStatement nvarchar(4000);
DECLARE @.paramDefinition nvarchar(500)

--Set Where Clause and parameter statement

IF @.JobID > 0
BEGIN
SET @.whereClause = ' WHERE job_id = @.update_job_id'
SET @.paramDefinition = N'@.update_job_id int'

--Build Update Statement

SET @.updateClause = null

IF @.AddressChg > 0
BEGIN
DECLARE @.update_address_id varchar(15)
SET @.update_address_id = @.AddressID
IF @.updateClause IS NULL
SET @.updateClause = 'address_id = ' + @.update_address_id
ELSE
SET @.updateClause = @.updateClause + ', address_id = ' + @.update_address_id
END

IF @.CustomerChg > 0
BEGIN
DECLARE @.update_customer_id varchar(15)
SET @.update_customer_id = @.CustomerID
IF @.updateClause IS NULL
SET @.updateClause = 'customer_id = ' + @.update_customer_id
ELSE
SET @.updateClause = @.updateClause + ', customer_id = ' + @.update_customer_id
END

IF @.ContactChg > 0
BEGIN
DECLARE @.update_contact_id varchar(15)
SET @.update_contact_id = @.ContactID
IF @.updateClause IS NULL
BEGIN
IF @.update_contact_id IS NULL
SET @.updateClause = 'contact_id = NULL'
ELSE
SET @.updateClause = 'contact_id = ' + @.update_contact_id
END
ELSE
BEGIN
IF @.update_contact_id IS NULL
SET @.updateClause = @.updateClause + ', contact_id = NULL'
ELSE
SET @.updateClause = @.updateClause + ', contact_id = ' + @.update_contact_id
END
END

IF @.PlanChg > 0
BEGIN
DECLARE @.update_plan_id varchar(15)
SET @.update_plan_id = @.PlanID
IF @.updateClause IS NULL
BEGIN
IF @.update_plan_id IS NULL
SET @.updateClause = 'plan_id = NULL'
ELSE
SET @.updateClause = 'plan_id = ' + @.update_plan_id
END
ELSE
BEGIN
IF @.update_plan_id IS NULL
SET @.updateClause = @.updateClause + ', plan_id = NULL'
ELSE
SET @.updateClause = @.updateClause + ', plan_id = ' + @.update_plan_id
END
END-- Complete SQL statement

IF @.updateClause <> null
BEGIN
SET @.sqlStatement = 'UPDATE [dbo].[Jobs] SET ' + @.updateClause + @.whereClause
EXEC sp_executesql @.sqlStatement,@.paramDefinition, @.update_job_id=@.JobID
END
END
GO

I know that this is a huge post, but I have pretty much exhausted my resources.

Thanks,

I didn't analyze the logic of the SP. But you are making the SP more complicated than necessary. Here are the reasons:

1. You are using dynamic SQL which can hurt performance

2. Your dynamic SQL code doesn't protect against SQL injection. So you can compromise your database/server if you haave more SPs like this that take string parameters

3. Since the UPDATE statement is executed dynamically you need to grant UPDATE permissions to all callers of the SP. This increases the attack surface of the database and grants more permissions to users than necessary. And it kind of defeats the purpose of having a SP. You might as well form the UPDATE statement on the client-side and execute it directly. You will get the same or probably better performance

4. Debugging code using dynamic SQL can be hard and difficult to maintain also as you have encountered

5. Lastly, you don't really gain that much by updating only the necessary columns. SQL Server has to do lot of work to locate the row to update and after you have located the row for update it doesn't matter in most cases if you update one or all of the columns. The only case where it matters is if you have lot of variable length columns that can overflow or increase the row size which will require more work. Otherwise you might as well update the entire row. You don't save anything in terms of performance or efficiency.

If you are not passing the old values (i.e., columns that were not changed) then you can use alternate approach below which doesn't require dynamic SQL:

UPDATE dbo.Jobs

SET address_id = CASE WHEN AddressChg > 0 THEN @.AddressID ELSE address_id END

, ...

WHERE job_id = @.update_job_id

The main point to note is that keeping transactions/calls light-weight gives the best performance and improves overall utilization of the server.

|||

Thank you very much for your reply. I agree that the dynamic SQL use opened us up to some danger, although this is a windows form application. I would love to get rid of it, however, I don't understand how the statement that you have shown works. The case I understand of course, it's the 'ELSE address_id END' that I don't understand. I don't have this value (address_id) from my application, only the 'THEN AddressID'. Is this address_id coming from the table update mechanism? If so, this is definitely the answer to most of my problems.

|||

The address_id in the ELSE clause comes from the row you are updating. It refers to the column in the table. So it is a way to use the current value of address_id if there is no change. SQL is a set-based language so statements like UPDATE has logically a before and after image for each row & the operations happen in one shot. This allows you to swap values in two columns using an UPDATE statement without any intermediate storage:

update t

set a = b, b = a

The database engine handles all the dirty work for you.

|||It works beautifully. Thank you so much for this elegant, simple solution.

Parameterized UDF in correlated subquery

Hello all,
I'm trying to run a query that looks like this:
UPDATE MyTable SET
ParsedField1 = (SELECT ParsedField1 FROM dbo.parseField(ot.FieldToParse)),
ParsedField2 = (SELECT ParsedField2 FROM dbo.parseField(ot.FieldToParse)),
ParsedField3 = (SELECT ParsedField3 FROM dbo.parseField(ot.FieldToParse)),
ParsedField4 = (SELECT ParsedField4 FROM dbo.parseField(ot.FieldToParse)),
FROM MyTable mt
INNER JOIN OtherTable ot ON mt.pid = ot.pid
<FieldtoParse> is a string that requires some fairly complicated logic to
parse so I encapsulated it in a UDF that returns a table resultset. However
,
using a parameterized UDF in a correlated subquery appears to be unsupported
.
It thinks it's a HINT! Can someone tell me if I'm right about that?
I have several other options I can use to get the same thing accomplished so
I'm not worried about that but this way would have been the cleanest.
Thanks for the help!
MikeWhy not to create a scalar function instead?
UPDATE MyTable SET
ParsedField1 = dbo.parseField(ot.FieldToParse, 1),
ParsedField2 = dbo.parseField(ot.FieldToParse, 2),
ParsedField3 = dbo.parseField(ot.FieldToParse, 3),
ParsedField4 = dbo.parseField(ot.FieldToParse, 4),
FROM MyTable mt
INNER JOIN OtherTable ot ON mt.pid = ot.pid
go
AMB
"Mike L" wrote:

> Hello all,
> I'm trying to run a query that looks like this:
> UPDATE MyTable SET
> ParsedField1 = (SELECT ParsedField1 FROM dbo.parseField(ot.FieldToParse)
),
> ParsedField2 = (SELECT ParsedField2 FROM dbo.parseField(ot.FieldToParse)
),
> ParsedField3 = (SELECT ParsedField3 FROM dbo.parseField(ot.FieldToParse)
),
> ParsedField4 = (SELECT ParsedField4 FROM dbo.parseField(ot.FieldToParse)
),
> FROM MyTable mt
> INNER JOIN OtherTable ot ON mt.pid = ot.pid
> <FieldtoParse> is a string that requires some fairly complicated logic to
> parse so I encapsulated it in a UDF that returns a table resultset. Howev
er,
> using a parameterized UDF in a correlated subquery appears to be unsupport
ed.
> It thinks it's a HINT! Can someone tell me if I'm right about that?
> I have several other options I can use to get the same thing accomplished
so
> I'm not worried about that but this way would have been the cleanest.
> Thanks for the help!
> Mike
>|||I can and did actually create one very similar to this. However, my
question is more academic. I haven't found anything saying this is
prohibited but it doesn't appear to work. I was just wondering why or if
there's a syntax "trick" you have to use to get it to work.
Thanks for the reply though!
Mike
"Alejandro Mesa" wrote:
> Why not to create a scalar function instead?
> UPDATE MyTable SET
> ParsedField1 = dbo.parseField(ot.FieldToParse, 1),
> ParsedField2 = dbo.parseField(ot.FieldToParse, 2),
> ParsedField3 = dbo.parseField(ot.FieldToParse, 3),
> ParsedField4 = dbo.parseField(ot.FieldToParse, 4),
> FROM MyTable mt
> INNER JOIN OtherTable ot ON mt.pid = ot.pid
> go
>
> AMB
> "Mike L" wrote:
>|||Mike
Can you show us an UDF? I'm pretty sure that Alejandro answers your
question.
"Mike L" <MikeL@.discussions.microsoft.com> wrote in message
news:230EE401-C561-45F6-8396-0F9E9E071A0D@.microsoft.com...
> I can and did actually create one very similar to this. However, my
> question is more academic. I haven't found anything saying this is
> prohibited but it doesn't appear to work. I was just wondering why or if
> there's a syntax "trick" you have to use to get it to work.
> Thanks for the reply though!
> Mike
> "Alejandro Mesa" wrote:
>

Parameterized SSIS Packeges

How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

did you try using the execute sql task, mapping the parameter to a variable?|||

JSR2005 wrote:

How i can create parameterized sql query .This is my basic idea to implement.
select * from dimemployee
where name = ?
or
exec proc sp_para_employee ?
how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

How hard did you look?

Execute SQL Task
(http://www.sqlis.com/default.aspx?58)

-Jamie

|||thank you it is working fine

Parameterized SP in WHERE Clause of Another SP

I've got this SP:
CREATE PROCEDURE
EWF_spCustom_AddProfiles_CompanyYear
@.prmSchoolYear char(11)
AS
SELECT
ContactID
FROM
dbo.EWF_tblCustom_CompanyProfile
WHERE
SchoolYear = @.prmSchoolYear

I'd like to be able to reference that in the where clause of another
SP. Is that possible?

I'd like to end up with something like this:
CREATE PROCEDURE
MyNewProc
@.prmSchoolYear2 char(11)
AS
SELECT
ContactID, SomeOtherFields
FROM
tblContact
WHERE
ContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear
@.prmSchoolYear2)

How would I make that happen?

If this isn't possible, what else might I try?

Thanks much for any pointers.

Jeremy

PS: I accidentally crossposted this in another group
(http://tinyurl.com/gksq4) thinking it was this one. Sorry for that.jeremygetsmail@.gmail.com (jeremygetsmail@.gmail.com) writes:
> I've got this SP:
> CREATE PROCEDURE
> EWF_spCustom_AddProfiles_CompanyYear
> @.prmSchoolYear char(11)
> AS
> SELECT
> ContactID
> FROM
> dbo.EWF_tblCustom_CompanyProfile
> WHERE
> SchoolYear = @.prmSchoolYear
> I'd like to be able to reference that in the where clause of another
> SP. Is that possible?
> I'd like to end up with something like this:
> CREATE PROCEDURE
> MyNewProc
> @.prmSchoolYear2 char(11)
> AS
> SELECT
> ContactID, SomeOtherFields
> FROM
> tblContact
> WHERE
> ContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear
> @.prmSchoolYear2)
> How would I make that happen?

First of all, if your code is as simple as that, it may not even
be worth the effort. Code reuse in all its glory, but it is not always
the best in a database. That is not to say that code reuse is not
a virtue in SQL at all, but just a little smaller virtue.

Anyway, this article of mine may give you some ideas:
http://www.sommarskog.se/share_data.html.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Parameterized query?

Can someone please help me with this parameterized query? Its is not working.


builder.Append("select datepart(dd, datetime) as 'Day', datepart(hh,datetime) as 'Hour', count(*) as 'Count' ");
builder.Append("from @.table_name with (nolock) ");
builder.Append("where datetime > @.date_time ");
builder.Append("group by datepart(dd, datetime), datepart(hh, datetime) ");
builder.Append("order by datepart(dd, datetime), datepart(hh, datetime");dateTime = DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongTimeString();
cmd.CommandType = CommandType.Text;
cmd.CommandText = builder.ToString();
cmd.Parameters.Add("@.table_name", tableName);
cmd.Parameters.Add("@.date_time", dateTime);

Please define "not working" and provide any error messages you are receiving.

I believe the problem is likely that you are attempting to use a parameter for the table name, which will not work and will generate an error from SQL Server. SQL Server does not provide for a variable as the table name in a query. You will instead need to append that table name as part of your string builder.

Terri|||Check out this tutorial on parameterized queries:
http://aspnet101.com/aspnet101/tutorials.aspx?id=1

Parameterized Query Using Wildcards in VS2005

Hey everyone,

I have a smart device project in Visual Studio 2005 that has a SQL Mobile data source. I am trying to create a parameterized query that utilizes 'LIKE' and wildcards. My query is below:

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE (StreetNum = @.StreetNum) AND (StreetName LIKE '%' + @.StreetName + '%')

However, when I test this on my PDA, I get the following error:

SQL Execution Error.

Executed SQL statement: SELECT LocationID, StreetNum, StreetName, rowguid FROM tblLocations WHERE (StreetNum = @.StreetNum) AND (StreetName LIKE '%' + @.StreetName + '%')
Error Source: SQL Server Mobile Edition ADO.NET Data Provider
Error Message: @.StreetName : deerbrook - FormatException

Does anyone know how to add wildcards to a parameter?

Thanks,

Lee

Hey,

This is an a stored proc, or in an ADO.NET query? What I've had to done in the past is to create a dynamic SQL string, and execute that string using exec or exec sp_executeSQL.

Brian

|||

Hey Brian,

Actually, this is a store proc that is created from within TableAdapter in VS2005.

Lee

|||

Hey,

Well then, being in code, I don't think you could use a variable with that kind of string append... you may have to hard code that value into the string, instead of using a variable. But being in a table adapter, I don't know if that will work. You could try passing the %% in with the string by yourself.

Brian

|||

change the select statement in the adapter wizard to this

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE(StreetNum = @.StreetNum) AND (StreetName LIKE @.StreetName)

change your event code to this

Private Sub FillByButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles FillByButton.Click
Try
Me.TblLocationsTableAdapter.FillBy(Me.WorkOrdersDataSet.tblLocations, _
StreetNumTextBox.Text, _
String.format("%{0}%",StreetNameTextBox.Text))
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub

Impotant to note - %value% will not utilize indexes and can lead to poor performance. just keep that in mind.

In places where we have a lot of data that may be seached like this, we provide a combo box with values "equals|begins with|ends with|contains" and attach %'s appropriately. Using the Above formatted SQL accomodates this.

cheers

|||

This question was already answered, but it did not provide you with the internals. You need to set the % in the parameters value.

You create for example the following query:

select * from FOO where BAR like @.P1;

After that you have to create a SqlCeParameter object and set the % in the value of that parameter:

SqlCeParameter parameter = new SqlCeParameter();
parameter.Value = string.Format("%{0}%", value);

The DataAdapter is hiding this here. But if you are working with SqlCeCommand and objects you have to know it.

|||

Are you able to query now with this answer? or still facing some issues?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Yes, Blair gave me the answer that I needed. Thanks.|||Hi All.

I have same problem with string concatenation.

SqlCeConnection con = new SqlCeConnection("Test.sdf");
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "select firstname + ' ' + lastname from person";
bool i = cmd.ExecuteReader().Read();
cmd.CommandText = "select firstname + @.p0 + lastname from person";
cmd.Parameters.Add("@.p0", " ");
i = cmd.ExecuteReader().Read();

The first query executes fine, but second throws FormatException.
It looks like db expects double value instead of string.|||

Try using Parameter.AddWithValue.

Thanks,

Laxmi

|||Thanks for reply. No difference, i get same result :(.
Here is complete test:

string fileName = "Test.sdf";
File.Delete(fileName);
SqlCeConnection con = new SqlCeConnection("data source=" + fileName);
SqlCeEngine eng = new SqlCeEngine(con.ConnectionString);
eng.CreateDatabase();
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "create table person (firstname nvarchar(100), lastname nvarchar(100))";
cmd.ExecuteNonQuery();
cmd.CommandText = "select firstname + ' ' + lastname from person";
bool i = cmd.ExecuteReader().Read();
cmd.CommandText = "select firstname + @.p0 + lastname from person";
cmd.Parameters.AddWithValue("@.p0", " ");
i = cmd.ExecuteReader().Read();|||

It was my oversight. Parameters can only be used for WHERE Clause. But I see that you are using for SELECT Clause. I really dont know what you are trying to achieve and started using PARAMETERS in SELECT Clause. Can you please elaborate on what is your problem, what is the context ..so that we can have better understanding before we reply.

Thanks,

Laxmi

|||I don't think it depends from context.

cmd.CommandText = "select * from person where firstname + @.p0 + lastname = 'f l";
cmd.Parameters.AddWithValue("@.p0", " ");

bool i = cmd.ExecuteReader().Read();

I this case i get:
The data type is not valid for the boolean operation. [ Data type (if known) = float,Data type (if known) = nvarchar ]

Actually i have generic sql generation system and want to decide - put string in text or pass it as a parameter to sql command.|||

Can you please try this?

cmd.CommandText = "select * from person where firstname = @.p0 AND lastname = 'f l";
cmd.Parameters.AddWithValue("@.p0", " ");

Thanks,

Laxmi

|||After adding missing quote at the end of command text, it executes just fine.
But this is not what i expect. It produces different result.

Parameterized Query Using Wildcards in VS2005

Hey everyone,

I have a smart device project in Visual Studio 2005 that has a SQL Mobile data source. I am trying to create a parameterized query that utilizes 'LIKE' and wildcards. My query is below:

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE (StreetNum = @.StreetNum) AND (StreetName LIKE '%' + @.StreetName + '%')

However, when I test this on my PDA, I get the following error:

SQL Execution Error.

Does anyone know how to add wildcards to a parameter?

Thanks,

Lee

Hey,

This is an a stored proc, or in an ADO.NET query? What I've had to done in the past is to create a dynamic SQL string, and execute that string using exec or exec sp_executeSQL.

Brian

|||

Hey Brian,

Actually, this is a store proc that is created from within TableAdapter in VS2005.

Lee

|||

Hey,

Brian

|||

change the select statement in the adapter wizard to this

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE(StreetNum = @.StreetNum) AND (StreetName LIKE @.StreetName)

change your event code to this

Impotant to note - %value% will not utilize indexes and can lead to poor performance. just keep that in mind.

cheers

|||

This question was already answered, but it did not provide you with the internals. You need to set the % in the parameters value.

You create for example the following query:

select * from FOO where BAR like @.P1;

After that you have to create a SqlCeParameter object and set the % in the value of that parameter:

SqlCeParameter parameter = new SqlCeParameter();
parameter.Value = string.Format("%{0}%", value);

The DataAdapter is hiding this here. But if you are working with SqlCeCommand and objects you have to know it.

|||

Are you able to query now with this answer? or still facing some issues?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Yes, Blair gave me the answer that I needed. Thanks.|||Hi All.

I have same problem with string concatenation.

The first query executes fine, but second throws FormatException.
It looks like db expects double value instead of string.|||

Try using Parameter.AddWithValue.

Thanks,

Laxmi

|||Thanks for reply. No difference, i get same result :(.
Here is complete test:

Thanks,

Laxmi

|||I don't think it depends from context.

cmd.CommandText = "select * from person where firstname + @.p0 + lastname = 'f l";
cmd.Parameters.AddWithValue("@.p0", " ");

bool i = cmd.ExecuteReader().Read();

I this case i get:
The data type is not valid for the boolean operation. [ Data type (if known) = float,Data type (if known) = nvarchar ]

Actually i have generic sql generation system and want to decide - put string in text or pass it as a parameter to sql command.|||

Can you please try this?

cmd.CommandText = "select * from person where firstname = @.p0 AND lastname = 'f l";
cmd.Parameters.AddWithValue("@.p0", " ");

Thanks,

Laxmi

|||After adding missing quote at the end of command text, it executes just fine.
But this is not what i expect. It produces different result.

Parameterized Query Using Wildcards in VS2005

Hey everyone,

I have a smart device project in Visual Studio 2005 that has a SQL Mobile data source. I am trying to create a parameterized query that utilizes 'LIKE' and wildcards. My query is below:

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE (StreetNum = @.StreetNum) AND (StreetName LIKE '%' + @.StreetName + '%')

However, when I test this on my PDA, I get the following error:

SQL Execution Error.

Does anyone know how to add wildcards to a parameter?

Thanks,

Lee

Hey,

This is an a stored proc, or in an ADO.NET query? What I've had to done in the past is to create a dynamic SQL string, and execute that string using exec or exec sp_executeSQL.

Brian

|||

Hey Brian,

Actually, this is a store proc that is created from within TableAdapter in VS2005.

Lee

|||

Hey,

Brian

|||

change the select statement in the adapter wizard to this

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE(StreetNum = @.StreetNum) AND (StreetName LIKE @.StreetName)

change your event code to this

Impotant to note - %value% will not utilize indexes and can lead to poor performance. just keep that in mind.

cheers

|||

This question was already answered, but it did not provide you with the internals. You need to set the % in the parameters value.

You create for example the following query:

select * from FOO where BAR like @.P1;

After that you have to create a SqlCeParameter object and set the % in the value of that parameter:

SqlCeParameter parameter = new SqlCeParameter();
parameter.Value = string.Format("%{0}%", value);

The DataAdapter is hiding this here. But if you are working with SqlCeCommand and objects you have to know it.

|||

Are you able to query now with this answer? or still facing some issues?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Yes, Blair gave me the answer that I needed. Thanks.|||Hi All.

I have same problem with string concatenation.

The first query executes fine, but second throws FormatException.
It looks like db expects double value instead of string.|||

Try using Parameter.AddWithValue.

Thanks,

Laxmi

|||Thanks for reply. No difference, i get same result :(.
Here is complete test:

Thanks,

Laxmi

|||I don't think it depends from context.

cmd.CommandText = "select * from person where firstname + @.p0 + lastname = 'f l";
cmd.Parameters.AddWithValue("@.p0", " ");

bool i = cmd.ExecuteReader().Read();

I this case i get:
The data type is not valid for the boolean operation. [ Data type (if known) = float,Data type (if known) = nvarchar ]

Actually i have generic sql generation system and want to decide - put string in text or pass it as a parameter to sql command.|||

Can you please try this?

cmd.CommandText = "select * from person where firstname = @.p0 AND lastname = 'f l";
cmd.Parameters.AddWithValue("@.p0", " ");

Thanks,

Laxmi

|||After adding missing quote at the end of command text, it executes just fine.
But this is not what i expect. It produces different result.

Parameterized Query Using Wildcards in VS2005

Hey everyone,

I have a smart device project in Visual Studio 2005 that has a SQL Mobile data source. I am trying to create a parameterized query that utilizes 'LIKE' and wildcards. My query is below:

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE (StreetNum = @.StreetNum) AND (StreetName LIKE '%' + @.StreetName + '%')

However, when I test this on my PDA, I get the following error:

SQL Execution Error.

Does anyone know how to add wildcards to a parameter?

Thanks,

Lee

Hey,

This is an a stored proc, or in an ADO.NET query? What I've had to done in the past is to create a dynamic SQL string, and execute that string using exec or exec sp_executeSQL.

Brian

|||

Hey Brian,

Actually, this is a store proc that is created from within TableAdapter in VS2005.

Lee

|||

Hey,

Brian

|||

change the select statement in the adapter wizard to this

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE(StreetNum = @.StreetNum) AND (StreetName LIKE @.StreetName)

change your event code to this

Impotant to note - %value% will not utilize indexes and can lead to poor performance. just keep that in mind.

cheers

|||

This question was already answered, but it did not provide you with the internals. You need to set the % in the parameters value.

You create for example the following query:

select * from FOO where BAR like @.P1;

After that you have to create a SqlCeParameter object and set the % in the value of that parameter:

SqlCeParameter parameter = new SqlCeParameter();
parameter.Value = string.Format("%{0}%", value);

The DataAdapter is hiding this here. But if you are working with SqlCeCommand and objects you have to know it.

|||

Are you able to query now with this answer? or still facing some issues?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||Yes, Blair gave me the answer that I needed. Thanks.|||Hi All.

I have same problem with string concatenation.

The first query executes fine, but second throws FormatException.
It looks like db expects double value instead of string.|||

Try using Parameter.AddWithValue.

Thanks,

Laxmi

|||Thanks for reply. No difference, i get same result :(.
Here is complete test:

Thanks,

Laxmi

|||I don't think it depends from context.

cmd.CommandText = "select * from person where firstname + @.p0 + lastname = 'f l";
cmd.Parameters.AddWithValue("@.p0", " ");
bool i = cmd.ExecuteReader().Read();

I this case i get:
The data type is not valid for the boolean operation. [ Data type (if known) = float,Data type (if known) = nvarchar ]

Actually i have generic sql generation system and want to decide - put string in text or pass it as a parameter to sql command.|||

Can you please try this?

cmd.CommandText = "select * from person where firstname = @.p0 AND lastname = 'f l";
cmd.Parameters.AddWithValue("@.p0", " ");

Thanks,

Laxmi

|||After adding missing quote at the end of command text, it executes just fine.
But this is not what i expect. It produces different result.

Parameterized query that will work for both Oracle and SQL Server

Hi, I have a dataset that I am using the following SQL DML to gather the data
select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate = ?
This works fine when the Shared Data Source is Oracle. However when I am
using a SQL Server Shared Data Source I need to change it to the following
with the Native SQL Client
select * from hermes.vwsurveyprepostresults where ssn = @.SSN and surveydate
= @.SURVEYDATE
However when I change it to use OLEDB for SQL Server as a shared data source
and revert to the ? as parameter place holders I get the following error -
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
Incorrect syntax near '?'. Incorrect syntax near '?'.
Is there a parameterized query that will work for both Oracle and SQL Server?
--
Thank you,
JohnTry to set Oracle a linked server and using store procedure as the query
string.
You can generate you SQL synatx in store procedure depending on the input
parameter.
HTH
> Hi, I have a dataset that I am using the following SQL DML to gather the
> data
> select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => ?
> This works fine when the Shared Data Source is Oracle. However when I am
> using a SQL Server Shared Data Source I need to change it to the following
> with the Native SQL Client
> select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> surveydate
> = @.SURVEYDATE
> However when I change it to use OLEDB for SQL Server as a shared data
> source
> and revert to the ? as parameter place holders I get the following error -
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> Incorrect syntax near '?'. Incorrect syntax near '?'.
> Is there a parameterized query that will work for both Oracle and SQL
> Server?
>
> --
> Thank you,
> John|||Is this the easiest way to do it? Seems like a hack, inelegant and not
appealing.
Currently I am trying to figure out how to use a Web Service as my data
source - XML. That way I can control via a config file which database to
pull from and the paremeters will be the same.
Both these solutions seem like a convulusion of what should be a relatively
easy thing to do. Pass an ANSI-SQL query a parameter using OLEDB as the
provider and have the parameter signature always be the same. I cannot
believe this is such a difficult thing. If I did not know better I would
swear that this is a bug.
--
Thank you,
John
"Steffi" wrote:
> Try to set Oracle a linked server and using store procedure as the query
> string.
> You can generate you SQL synatx in store procedure depending on the input
> parameter.
> HTH
> > Hi, I have a dataset that I am using the following SQL DML to gather the
> > data
> > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > ?
> >
> > This works fine when the Shared Data Source is Oracle. However when I am
> > using a SQL Server Shared Data Source I need to change it to the following
> > with the Native SQL Client
> > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > surveydate
> > = @.SURVEYDATE
> >
> > However when I change it to use OLEDB for SQL Server as a shared data
> > source
> > and revert to the ? as parameter place holders I get the following error -
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > Incorrect syntax near '?'. Incorrect syntax near '?'.
> >
> > Is there a parameterized query that will work for both Oracle and SQL
> > Server?
> >
> >
> > --
> > Thank you,
> > John
>
>|||If you use ODBC against both then you will be able to use the same query.
But, keep in mind that Oracle and SQL Server can have different syntax. But,
if you are using the more recent Oracle (like 9i or 10i ... not sure what
the latest version is) then Oracle has started to support join syntax (inner
join, left join etc). Otherwise, if you get complicated at all then the
syntax can vary.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> Hi, I have a dataset that I am using the following SQL DML to gather the
> data
> select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => ?
> This works fine when the Shared Data Source is Oracle. However when I am
> using a SQL Server Shared Data Source I need to change it to the following
> with the Native SQL Client
> select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> surveydate
> = @.SURVEYDATE
> However when I change it to use OLEDB for SQL Server as a shared data
> source
> and revert to the ? as parameter place holders I get the following error -
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> Incorrect syntax near '?'. Incorrect syntax near '?'.
> Is there a parameterized query that will work for both Oracle and SQL
> Server?
>
> --
> Thank you,
> John|||Hi John,
Thank you for your post.
OLE DB is a Microsoft's strategic low-level application program interface
(API) for access to different data sources.
An application using OLE DB would use this request sequence:
Initialize OLE.
Connect to a data source.
Issue a command.
Process the results.
Release the data source object and uninitialize OLE.
The Oledb provide just pass the sql command to the database engine to
execute and get the recordset from the engine.
Since the syntax of SQL Server only support the variable marked with @., you
could not use other mark to specify it's a parameter.
I think Steffi's suggestion is a great suggest for you. You could add a
linked server in the sql server and use the stored procedure to get the
result.
Configuring Linked Servers
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_server_4uuq.asp
Hope this will be helpful!
Sincerely,
Wei Lu
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.|||Thanks for all the help guys but I am leaning more toward using a Web Service
AKA XML as my data source. Played around with it over the weekend and got it
working. This actually has a number of benefits for me. The main one being
greater control over what is happening from a programatic stand point. I
have nothing against using Stored Procedures per se but I think that I would
run into problems linking the databases as I do not have much control over
our Oracle databases.
--
Thank you,
John
"Wei Lu" wrote:
> Hi John,
> Thank you for your post.
> OLE DB is a Microsoft's strategic low-level application program interface
> (API) for access to different data sources.
> An application using OLE DB would use this request sequence:
> Initialize OLE.
> Connect to a data source.
> Issue a command.
> Process the results.
> Release the data source object and uninitialize OLE.
> The Oledb provide just pass the sql command to the database engine to
> execute and get the recordset from the engine.
> Since the syntax of SQL Server only support the variable marked with @., you
> could not use other mark to specify it's a parameter.
> I think Steffi's suggestion is a great suggest for you. You could add a
> linked server in the sql server and use the stored procedure to get the
> result.
> Configuring Linked Servers
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _1_server_4uuq.asp
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> 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.
>|||Thanks Bruce, this seems like the easiest and best short term solution.
--
Thank you,
John
"Bruce L-C [MVP]" wrote:
> If you use ODBC against both then you will be able to use the same query.
> But, keep in mind that Oracle and SQL Server can have different syntax. But,
> if you are using the more recent Oracle (like 9i or 10i ... not sure what
> the latest version is) then Oracle has started to support join syntax (inner
> join, left join etc). Otherwise, if you get complicated at all then the
> syntax can vary.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> > Hi, I have a dataset that I am using the following SQL DML to gather the
> > data
> > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > ?
> >
> > This works fine when the Shared Data Source is Oracle. However when I am
> > using a SQL Server Shared Data Source I need to change it to the following
> > with the Native SQL Client
> > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > surveydate
> > = @.SURVEYDATE
> >
> > However when I change it to use OLEDB for SQL Server as a shared data
> > source
> > and revert to the ? as parameter place holders I get the following error -
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > Incorrect syntax near '?'. Incorrect syntax near '?'.
> >
> > Is there a parameterized query that will work for both Oracle and SQL
> > Server?
> >
> >
> > --
> > Thank you,
> > John
>
>|||John,
I wanted to put this post because I was trying to do the same thing that you
are, using parameters with Reporting Services to hit an Oracle DB.
The only way that I found out how to get this to work is by using a ":"
instead of the "@.".
So, for example:
SELECT *
FROM sometable x
WHERE x.auth_date >= to_date(:param_start, 'mm/dd/yyyy')
Then I set my Report Parameter(param_start) as a string value.
It works fine for me right now, I do wonder when I try to roll it up into a
stored proc that if it will work or not. I hope this helps others out there.
Rob Cuscaden
"John A" wrote:
> Thanks Bruce, this seems like the easiest and best short term solution.
> --
> Thank you,
> John
>
> "Bruce L-C [MVP]" wrote:
> > If you use ODBC against both then you will be able to use the same query.
> > But, keep in mind that Oracle and SQL Server can have different syntax. But,
> > if you are using the more recent Oracle (like 9i or 10i ... not sure what
> > the latest version is) then Oracle has started to support join syntax (inner
> > join, left join etc). Otherwise, if you get complicated at all then the
> > syntax can vary.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "John A" <i-code4food@.newsgroups.nospam> wrote in message
> > news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> > > Hi, I have a dataset that I am using the following SQL DML to gather the
> > > data
> > > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > > ?
> > >
> > > This works fine when the Shared Data Source is Oracle. However when I am
> > > using a SQL Server Shared Data Source I need to change it to the following
> > > with the Native SQL Client
> > > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > > surveydate
> > > = @.SURVEYDATE
> > >
> > > However when I change it to use OLEDB for SQL Server as a shared data
> > > source
> > > and revert to the ? as parameter place holders I get the following error -
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
> > > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > > Incorrect syntax near '?'. Incorrect syntax near '?'.
> > >
> > > Is there a parameterized query that will work for both Oracle and SQL
> > > Server?
> > >
> > >
> > > --
> > > Thank you,
> > > John
> >
> >
> >|||Bruce -- I am just trying your suggestion, since I have the same issue. In
my query, how do I write the parameters (with a '?', ':' or '@.')?
I have tried all 3 with no success.
When I try '?' - I get an error saying that "Cannot add multi value query
parameter '?' for data set 'Report_main' because it is not supported by the
data extension."
When I try ':' - it says incorrect syntax near ':'
When I try '@.' - it says "The data extension ODBC does not support named
parameters. Use unnamed parameters instead."
Thanks in advance for your help.
--
LaurieT
"Bruce L-C [MVP]" wrote:
> If you use ODBC against both then you will be able to use the same query.
> But, keep in mind that Oracle and SQL Server can have different syntax. But,
> if you are using the more recent Oracle (like 9i or 10i ... not sure what
> the latest version is) then Oracle has started to support join syntax (inner
> join, left join etc). Otherwise, if you get complicated at all then the
> syntax can vary.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> > Hi, I have a dataset that I am using the following SQL DML to gather the
> > data
> > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > ?
> >
> > This works fine when the Shared Data Source is Oracle. However when I am
> > using a SQL Server Shared Data Source I need to change it to the following
> > with the Native SQL Client
> > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > surveydate
> > = @.SURVEYDATE
> >
> > However when I change it to use OLEDB for SQL Server as a shared data
> > source
> > and revert to the ? as parameter place holders I get the following error -
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > Incorrect syntax near '?'. Incorrect syntax near '?'.
> >
> > Is there a parameterized query that will work for both Oracle and SQL
> > Server?
> >
> >
> > --
> > Thank you,
> > John
>
>|||Hello Laurie,
Have you tried the suggestion Rob Provided?
===========================From: =?Utf-8?B?Um9i?= <Rob@.discussions.microsoft.com>
Subject: Re: Parameterized query that will work for both Oracle and SQL Ser
Date: Fri, 21 Jul 2006 09:39:01 -0700
Newsgroups: microsoft.public.sqlserver.reportingsvcs
John,
I wanted to put this post because I was trying to do the same thing that
you
are, using parameters with Reporting Services to hit an Oracle DB.
The only way that I found out how to get this to work is by using a ":"
instead of the "@.".
So, for example:
SELECT *
FROM sometable x
WHERE x.auth_date >= to_date(:param_start, 'mm/dd/yyyy')
Then I set my Report Parameter(param_start) as a string value.
It works fine for me right now, I do wonder when I try to roll it up into a
stored proc that if it will work or not. I hope this helps others out
there.
Rob Cuscaden
===========================
Sincerely,
Wei Lu
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.|||Yes -- I think so.
I was already using ":" variables in my query. But I was connecting to the
database using an Oracle driver. So I set up a ODBC connection to my SQL
Server database, then I created a new datasource using ODBC and pointed to
the SQL Server ODBC connection. I get the following error: incorrect syntax
near the ':'.
I must be missing something?
--
LaurieT
"Wei Lu [MSFT]" wrote:
> Hello Laurie,
> Have you tried the suggestion Rob Provided?
> ===========================> From: =?Utf-8?B?Um9i?= <Rob@.discussions.microsoft.com>
> Subject: Re: Parameterized query that will work for both Oracle and SQL Ser
> Date: Fri, 21 Jul 2006 09:39:01 -0700
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> John,
> I wanted to put this post because I was trying to do the same thing that
> you
> are, using parameters with Reporting Services to hit an Oracle DB.
> The only way that I found out how to get this to work is by using a ":"
> instead of the "@.".
> So, for example:
> SELECT *
> FROM sometable x
> WHERE x.auth_date >= to_date(:param_start, 'mm/dd/yyyy')
> Then I set my Report Parameter(param_start) as a string value.
> It works fine for me right now, I do wonder when I try to roll it up into a
> stored proc that if it will work or not. I hope this helps others out
> there.
> Rob Cuscaden
> ===========================> Sincerely,
> Wei Lu
> 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.
>|||I've been working with some of the same issues and found that you can't
really blur the like between the Oracle and SQL Server data.
You have to query the Oracle db with Oracle syntax. If you want to roll
everything up to a SQL Server OLTP or OLAP db, design an SSIS package
and import your Oracle & SQL data into a single source.
I know it's not the answer you wanted, but in the end it will be faster.
Garth H
webdev511@.spamcop.net
Microsoft Certified Professional
Macromedia Certified Developer|||Hello Laurie,
I agree with Garth that design a SSIS package(in SQL 2005) or a DTS package
(in SQL 2000) will be a faster way to do this.
Also, you may try to build up a Linked server in SQL Server which point to
the Oracle database.
Sincerely,
Wei Lu
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.|||Hello,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
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.

parameterized query that counts the number of null values in an int32 column

Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:

cmd.Add(new SqlCeParameter("@.IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";

cmd.Add(new SqlCeParameter("@.IntColumn", DBNull.Value));

cmd.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";

cmd.Add(new SqlCeParameter("@.IntColumn", SqlInt32.Null));

cmd.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";

cmd.Add(new SqlCeParameter("@.IntColumn", DBNull.Value));

cmd.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));

cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;

cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));

cmd.Parameters["@.IntColumn"].Value = DBNull.Value;

cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));

cmd.Parameters["@.IntColumn"].Value = DBNull.Value;

cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));

cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @.IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));

cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;

cmd.CommandText = "select count(*) from Meter where not IntColumn = @.IntColumn";

md.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));

cmd.Parameters["@.IntColumn"].Value = DBNull.Value;

cmd.CommandText = "select count(*) from Meter where IntColumn is not @.IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;

cmd.CommandText = "select count(*) from Meter where not IntColumn = @.IntColumn";

Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.

try using this syntax:

WHERE [columnName] != DBNull.Value or WHERE [columnName] <> DBNull.Value

Darren

|||I could not get that to work. Could you provide a more detailed examle of what the c# code would actually look like?|||

I think you've found yourself a bug in the ADO provider for SQL Mobile. I tried this code on the Northwind database and it reports 0 rows (should be 22):

SqlCeConnection cn = new SqlCeConnection(@."Data Source = \program files\deviceapplication7\Northwind.sdf");

try

{

cn.Open();

SqlCeCommand cmd = new SqlCeCommand("SELECT count(*) FROM CUSTOMERS WHERE FAX = @.fax", cn);

SqlCeParameter p1 = new SqlCeParameter("@.fax", DBNull.Value);

cmd.Parameters.Add(p1);

object o = cmd.ExecuteScalar();

if ( o != null && o != DBNull.Value)

{

MessageBox.Show("There were " + ((int)o).ToString() + " null fax numbers");

}

else

MessageBox.Show("Result of query was null");

This only occurs with a parameterized query however. If you use the normal syntax (IS NULL) in your query, you'll get the right results.

Darren

|||

Thanks for your response Daren. I started a new project and ran a program a program with the very similar code as what you tried and I got the same result. I was then wondering if the limitation/bug might be related to using SqlCeCommand.ExecuteScalar with parameterized queries in general rather than specifically looking for null or not null. So I tried this program and it returned a count of 1 which is correct:

SqlCeConnection conn = new SqlCeConnection(@."Data Source= \Program Files\BugTesting\Northwind.sdf;");

conn.Open();

SqlCeCommand cmd = conn.CreateCommand();

cmd.Parameters.Add(new SqlCeParameter("@.fax", SqlDbType.NVarChar, 24));

cmd.CommandText = "select COUNT(*) from Customers where Fax = @.fax";

cmd.Prepare();

cmd.Parameters["@.fax"].Value = "030-0076545";

object o = cmd.ExecuteScalar();

if (o != null && o != DBNull.Value)

{

MessageBox.Show("There were " + ((int)o).ToString() + " null fax numbers");

}

else

{

MessageBox.Show("Result of query was null");

}

conn.Close();

conn.Dispose();

This shows that you can do parameterized queries with count and execute scalar, but I still have not found a way to count the number of null values. I don't really know how parameterized queries work under the hood, but I would guess that the code that Darren posted would translate into something like this:

SELECT count(*) FROM CUSTOMERS WHERE FAX = null

If this is true then that would explain why it returns a count of 0. Which is why in my original post I was trying things more along the lines of: cmd.CommandText = "select count(*) from Customers where Fax is @.fax"

but that generates an SqlCeException. Is this a limitation, a bug, or is there still maybe another way to do this? Anyway the only reason why I was trying to do this as a parameterized query is because in my code runs it multiple times in a row, and I thought it might run faster this way. Even if I could get this to work somehow do think that it would improve the execution speed of the query? Also where is the best place to report this as a potential bug?

parameterized query string in Openrowset

Hi,
I have stored procedure in which i'm using the OpenRowSet method to fetch
data from an ODBC datasource.
The OpenRowSet method takes a query string to query the source.
I would like to add the parameters being passed to the calling stored
procedure to this query string.
unfortunately OPenRowSet does not accept string concatenation.
any suggestions on how to accomplish this would be really helpful.
Regards,
nabeelAccording to BOL , OPENROWSET doesn't accept variables. have you tried with
dynamic sql?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:516445D3-DE8B-40FA-9B70-D49107BCB382@.microsoft.com...
> Hi,
> I have stored procedure in which i'm using the OpenRowSet method to fetch
> data from an ODBC datasource.
> The OpenRowSet method takes a query string to query the source.
> I would like to add the parameters being passed to the calling stored
> procedure to this query string.
> unfortunately OPenRowSet does not accept string concatenation.
> any suggestions on how to accomplish this would be really helpful.
> Regards,
> nabeel|||Jack I'm not very proficient on T-SQL
can you please give me an example.
Thanks
"Jack Vamvas" wrote:

> According to BOL , OPENROWSET doesn't accept variables. have you tried wit
h
> dynamic sql?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:516445D3-DE8B-40FA-9B70-D49107BCB382@.microsoft.com...
>
>|||Jack I'm not very proficient on T-SQL
can you please give me an example.
Thanks
"Jack Vamvas" wrote:

> According to BOL , OPENROWSET doesn't accept variables. have you tried wit
h
> dynamic sql?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:516445D3-DE8B-40FA-9B70-D49107BCB382@.microsoft.com...
>
>

Parameterized query returns one row with null values.

I am hoping someone could help me understand why this is happening and perhaps a solution.

I am using ASP.NET 2.0 with a SQL 2005 database.

In code behind, I am performing a query using a parameter as below:

sql = "SELECT field_name FROM myTable WHERE (field_name = @.P1)"

objCommand.Parameters.Add(New SqlParameter("@.P1", TextBox1.Text))

The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.

The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:

sql =String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)

I also get NO records retuned using the same criteria.

What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?

Thanks!

I am not sure but see if adding the datatype helps:

objCommand.Parameters.Add(New SqlParameter("@.P1", SqlDbType.Varchar,30)).value = TextBox1.Text

|||

Thanks for the suggestion. I tried adding the data type as you suggested. It did not change the results.

I have found that if I change to a data reader, the null value is not being returned. So, now it looks to be related to the ExecuteScalar method.

|||

I also just realized that it is not a null value being returned but instead an empty value, ie "".

I can get around this easily enough in multiple ways, I am just wanting to understand why this is happening.

So far I have this narrowed down to the following:

A parameterized query, with a user account having select, insert, update permission, and using the ExecuteScalar method. This combination returns a record with an empty result when the criteria is not found in the table instead of returning no records at all.

|||

Eh?

ExecuteScalar is used to return the first column of the first row of the query. If there is no rows, the value comes back as null.

I think perhaps you are misunderstanding what ExecuteScalar is supposed to do. It doesn't return records, or recordsets, it returns a singular scalar value (One column of one row - the first of each).

For further help, please post the whole code block in question. How you initialize your connection, command objects, how you are actually executing the query, where you are storing the result of the query (And how it is defined), and what you expected the result to be, and what you actually got.

If the results are varying depending on what user is executing the query, please make sure that either you explicitly define the schema you want to use, or that there doesn't exist multiple tables with the same name under different schemas (Refer to the table as dbo.Table not just Table).

|||

Ok, my bad, stupid mistake(s) with both user permissions and also with the string.format method.

I at least have it consistenly returning the empty record.

One last question, why return null/empty instead of just nothing like a data reader?

Thank you very much for the response.

|||

Hi,

ExecuteScalar is designed to return a single value from a database command and the proper representation of a single non-existant value is returning null. The ExecuteScalar is a non-void method and should return something!

Enjoy C#,

Mehrdad

|||

Thank you to everyone for the help and clairification on ExecuteScalar.

Parameterized Query Question

I am trying to use the following SQL query to return a set of values:

SELECT id, submit_date, company_name, request_type, status
FROM tblRequestForms
WHERE request_type IN (@.RequestType) AND status IN (@.Status)
ORDER BY id ASC

I have tried passing an array of string values to both @.RequestType and @.Status, but It does not work. Is there any way to pass multiple values like this using parameters?

Thanks,
AaronI wish it were that simple, but alas it's not.

This has been discussed on several threads. User Ghan has pretty much summed up the different ways you can accomplish this in this post:view post 308485

Terri

parameterized query on sql 2005,2000 strange, simple example

Hi,
Can somebody please explain why the bellow described difference happens when
executing the same query with parameters and without, db set to
compatibility level 2000, so the same happens on sql 2000:
This query with parameters returns some result, although it should return
none:
exec sp_executesql N'select distinct top 50 name from LOCATION where (name
like @.ParamVal) and ( name < @.ParamLess ) order by 1 desc',N'@.ParamVal
nvarchar(4000),@.ParamLess
nvarchar(4000)',@.ParamVal=N'A%',@.ParamLess=N'A-100'
If I substitute parameters with values it returns proper result, i.e.
doesn't return anything as it should be:
select distinct top 50 name from LOCATION where (name like 'A%') and ( name
< 'A-100' ) order by 1 desc
Thank you
Vadim
Figured this out myself,
Thank you
"Vadim" <vadim@.dontsend.com> wrote in message
news:uJIRy1mOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Can somebody please explain why the bellow described difference happens
> when executing the same query with parameters and without, db set to
> compatibility level 2000, so the same happens on sql 2000:
> This query with parameters returns some result, although it should return
> none:
> exec sp_executesql N'select distinct top 50 name from LOCATION where (name
> like @.ParamVal) and ( name < @.ParamLess ) order by 1 desc',N'@.ParamVal
> nvarchar(4000),@.ParamLess
> nvarchar(4000)',@.ParamVal=N'A%',@.ParamLess=N'A-100'
> If I substitute parameters with values it returns proper result, i.e.
> doesn't return anything as it should be:
> select distinct top 50 name from LOCATION where (name like 'A%') and (
> name < 'A-100' ) order by 1 desc
> Thank you
> Vadim
>
>

Tuesday, March 20, 2012

Parametric Views

Parametric Views

Parametric Views

Monday, February 20, 2012

Parameterized where clause

Parameterized View or Parameterized Stored Procedure

parameterized update sp_executesql

Parameterized UDF in correlated subquery

Parameterized SSIS Packeges

Parameterized SP in WHERE Clause of Another SP

Parameterized query?

Parameterized Query Using Wildcards in VS2005

Parameterized Query Using Wildcards in VS2005

Parameterized Query Using Wildcards in VS2005

Parameterized Query Using Wildcards in VS2005

Parameterized query that will work for both Oracle and SQL Server

parameterized query that counts the number of null values in an int32 column

parameterized query string in Openrowset

Parameterized query returns one row with null values.

Parameterized Query Question

parameterized query on sql 2005,2000 strange, simple example

Parameterized Expression

Blog Archive

About Me