Query programmable

Hi,
I need to create a stored procedures that can pass table parameters in
varchar format.
for example:
CREATE FUNCTION MyFunc (@.table varchar(200)
AS
UPDATE @.table + 'aaa' -> How can I do this?
SET field1=tbl2.field2
FROM tbl2
WHERE tbl2.field3>10Use Dynamic SQL
Madhivanan|||You can try using dynamic SQLs
like this ..
declare @.t nvarchar(1000)
set @.t = 'UPDATE ' +@.table + 'aaa ' +
'SET field1=tbl2.field2 FROM tbl2 WHERE tbl2.field3>10 '
exec sp_executeSQL @.t|||UPDATE statements are not allowed in functions. Dynamic SQL is also
disallowed in functions. Perhaps a stored procedure will address your
needs.
Check out http://www.sommarskog.se/dynamic_sql.html for dynamic SQL
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dario Concilio" <DarioConcilio@.discussions.microsoft.com> wrote in message
news:F6C4572F-6306-47CB-9087-5C3E2204C824@.microsoft.com...
> Hi,
> I need to create a stored procedures that can pass table parameters in
> varchar format.
> for example:
> CREATE FUNCTION MyFunc (@.table varchar(200)
> AS
> UPDATE @.table + 'aaa' -> How can I do this?
> SET field1=tbl2.field2
> FROM tbl2
> WHERE tbl2.field3>10|||I can't see why that would make sense as a requirement unless your
tables duplicated lots of non-key columns. In that case it's really a
design problem rather than something to solve with a parameterized
UPDATE.
Dynamic SQL is one way you can do this but in a well-designed database
it should rarely be necessary.
David Portas
SQL Server MVP
--|||>> I need to create a stored procedures that can pass table parameters
in varchar format. <<
The short answer is use slow, proprietrary dynamic SQL to kludge a
query together on the fly with your table name in the FROM clause.
The right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your applications a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Yes, you can write a program with dynamic SQL to kludge something like
this. It will last about a year in production and then your data
integrity is shot.sql

query problem

i have a msde 2000 database, and one of the field are filled by datetime with the format "yyyy-mm-dd hh:mm:ss".
I make a query like this:
"select * from mytable where myfield like '%2006-06-15%'"
but i didn't get any record for the result and i have make it sure that there are lot of records with that value in myfield. The main problem is the char "-" that separate the date value, becouse if i make a query :
"select * from mytable" then i get all of my records.
Is there any suggest to solf my problem?why u want a like clause on a date time field.

Instead try myfield >= '2006-06-15' and myfield < '2006-06-16'|||Is your date field a datetime datatype, or is it a string?|||Your date is actually formatted more like 0x000097E2008BE35B even though SQL Server displays it as 2006-06-16 13:29 when it returns a value to the client machine. You can use string patterns on it, and SQL Server will happily convert for you, but it isn't efficient and worse yet it isn't always predictable.

The suggested syntax that Ronin gave is both efficient and predictable.

-PatP|||0x000097E2008BE35B?

Is is that late already? I missed my 0x000097E200A4CB80 meeting!

query problem

Hi i just dont know how to start on this,
the problem is that i want to generate a result on an accounting format for a set of reciepts and payments:
The scenario is as attached below:something like

select a.account_id ,
date = a.date,
"Invoice/CollectionId" = a.id ,
Debit = case when type = 1 then right(space(20) + convert(varchar(20),a.amount),20) else '' end ,
Credit = case when type = 2 then right(space(20) + convert(varchar(20),a.amount),20) else '' end
from
(
select id = i.invoice_id, i.amount, date = i.invoice_date, i.order_id, o.account_id from invoices i, order o where o.order_id = i.order_id, type = 1
union
select id = c.collection_id, c.amount, c.collection_date, null, c.account_id, 2 from collections c
) as amount a
order by a.account_id , a.date descsql

Query performance

This is a multi-part message in MIME format.
--=_NextPart_000_0019_01C6AC39.4D136D70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello,
I wrote the query below, but I was wondering whether there was a better = way of doing so. I am basically trying to build a recursive process.
Any suggestion would be greatly appreciated.
Thanks.
Mike
*************************************************************************= ********************
*************************************************************************= ********************
CREATE PROCEDURE dbo.CustomersFiltered
(
@.filter varchar(50)
)
AS
create table #t
(
dummyId bigint IDENTITY(1,1) PRIMARY KEY,
customerId bigint,
parentId bigint,
levelId int
)
INSERT #t (customerId, parentId, levelId)
SELECT customerAutoId, parentCustomerAutoId, 0
FROM customers
WHERE [name] like @.filter
declare @.dummyId bigint
select @.dummyId =3D 1
declare @.customerId bigint
declare @.parentId bigint
declare @.levelId bigint
WHILE (SELECT COUNT(*) FROM #t WHERE dummyId =3D @.dummyId) > 0
BEGIN
select @.customerId =3D customerId,
@.parentId =3D parentId,
@.levelId =3D levelId
FROM #t WHERE dummyId =3D @.dummyId
insert into #t (customerId, parentId, levelId)
select CustomerAutoId,
parentCustomerAutoId,
@.levelId - 1
from customers
where parentCustomerAutoId =3D @.customerId
group by CustomerAutoId,
parentCustomerAutoId
if (@.parentId is not null)
insert into #t (customerId, parentId, levelId)
select c.CustomerAutoId,
c.parentCustomerAutoId,
@.levelId + 1
from customers c
where c.customerAutoId =3D @.parentId
and not exists (select * from #t where c.customerAutoId =3D = customerId)
group by CustomerAutoId,
parentCustomerAutoId
select @.dummyId =3D @.dummyId + 1
END
select t.levelId as LevelId,
t.customerId as CustomerAutoId,
c.[name] as [Name],
c.sic as SIC,
c.ParentCustomerAutoId as ParentCustomerAutoId
from #t t
inner join customers c on c.customerAutoId =3D t.customerId
group by t.levelId,
t.customerId,
c.[name],
c.sic,
c.ParentCustomerAutoId
order by levelId desc,
c.ParentCustomerAutoId,
c.[name]
drop table #t
GO
--=_NextPart_000_0019_01C6AC39.4D136D70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Hello,

I wrote the query below, but I was = wondering whether there was a better way of doing so. I am basically trying to = build a recursive process.

Any suggestion would be greatly appreciated.

Thanks.
Mike


****************************************************************= *****************************
****************************************************************= *****************************
CREATE PROCEDURE dbo.CustomersFiltered( @.filter varchar(50))AS

create table #t( dummyId bigint IDENTITY(1,1) = PRIMARY KEY, customerId = bigint, parentId bigint, levelId int)

INSERT #t (customerId, parentId, levelId)SELECT = customerAutoId, parentCustomerAutoId, 0FROM customersWHERE [name] like = @.filter


declare @.dummyId bigintselect @.dummyId =3D 1

declare @.customerId bigintdeclare @.parentId bigintdeclare @.levelId bigint

WHILE (SELECT COUNT(*) FROM #t WHERE dummyId =3D @.dummyId) > 0BEGIN

select @.customerId =3D customerId, @.parentId =3D parentId, @.levelId =3D levelId FROM #t WHERE dummyId =3D @.dummyId

insert into #t (customerId, parentId, = levelId) select = CustomerAutoId, = parentCustomerAutoId, = @.levelId - 1 from customers where parentCustomerAutoId =3D @.customerId group by CustomerAutoId, = parentCustomerAutoId

if (@.parentId is not null) = insert into #t (customerId, parentId, levelId) = select c.CustomerAutoId, &nbs= p; c.parentCustomerAutoId, &nbs= p; @.levelId + 1 from customers c where c.customerAutoId =3D @.parentId and not exists (select = * from #t where c.customerAutoId =3D customerId) = group by CustomerAutoId, = parentCustomerAutoId

select @.dummyId =3D @.dummyId + 1

END

select t.levelId &nbs= p; as LevelId, t.customerId &= nbsp; as CustomerAutoId, c.[name]  = ; as [Name], c.sic &n= bsp; as SIC, c.ParentCustomerAutoId as ParentCustomerAutoIdfrom #t t inner join customers c on c.customerAutoId =3D = t.customerIdgroup by t.levelId, t.customerId, c.[name], c.sic, c.ParentCustomerAutoIdorder by levelId desc, c.ParentCustomerAutoId, &nbs= p; c.[name]

drop table #tGO


--=_NextPart_000_0019_01C6AC39.4D136D70--If you're using SQL Server 2005, you should consider using Common Table
Expressions (CTE) instead. Temporary tables suffer from writing data to
the tempdb database, whereas CTEs are in memory only.
A good walktrough is available at the MSDN site:
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
Hope this helps
"Mike" <none@.none.com> wrote in
news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
> I wrote the query below, but I was wondering whether there was a
> better way of doing so. I am basically trying to build a recursive
> process.
> Any suggestion would be greatly appreciated.
> Thanks.
> Mike
>
> **********************************************************************
*
> **********************
> **********************************************************************
*
> **********************
> CREATE PROCEDURE dbo.CustomersFiltered
> (
> @.filter varchar(50)
> )
> AS
>
> create table #t
> (
> dummyId bigint IDENTITY(1,1) PRIMARY KEY,
> customerId bigint,
> parentId bigint,
> levelId int
> )
>
> INSERT #t (customerId, parentId, levelId)
> SELECT customerAutoId, parentCustomerAutoId, 0
> FROM customers
> WHERE [name] like @.filter
>
> declare @.dummyId bigint
> select @.dummyId = 1
>
> declare @.customerId bigint
> declare @.parentId bigint
> declare @.levelId bigint
> WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
> BEGIN
> select @.customerId = customerId,
> @.parentId = parentId,
> @.levelId = levelId
> FROM #t
> WHERE dummyId = @.dummyId
> insert into #t (customerId, parentId, levelId)
> select CustomerAutoId,
> parentCustomerAutoId,
> @.levelId - 1
> from customers
> where parentCustomerAutoId = @.customerId
> group by CustomerAutoId,
> parentCustomerAutoId
> if (@.parentId is not null)
> insert into #t (customerId, parentId, levelId)
> select c.CustomerAutoId,
> c.parentCustomerAutoId,
> @.levelId + 1
> from customers c
> where c.customerAutoId = @.parentId
> and not exists (select * from #t where c.customerAutoId => customerId)
> group by CustomerAutoId,
> parentCustomerAutoId
>
> select @.dummyId = @.dummyId + 1
> END
>
> select t.levelId as LevelId,
> t.customerId as CustomerAutoId,
> c.[name] as [Name],
> c.sic as SIC,
> c.ParentCustomerAutoId as ParentCustomerAutoId
> from #t t
> inner join customers c on c.customerAutoId = t.customerId
> group by t.levelId,
> t.customerId,
> c.[name],
> c.sic,
> c.ParentCustomerAutoId
> order by levelId desc,
> c.ParentCustomerAutoId,
> c.[name]
>
> drop table #t
> GO
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||I am using SQL Server 2000 SP4.
Is there anything that I could improve in my query? I checked all indexes,
even in the temp table, but I ran out of ideas.
Thanks.
Mike
"Ole Kristian Bangås" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns98075D16535A0olekristianbangaas@.207.46.248.16...
> If you're using SQL Server 2005, you should consider using Common Table
> Expressions (CTE) instead. Temporary tables suffer from writing data to
> the tempdb database, whereas CTEs are in memory only.
> A good walktrough is available at the MSDN site:
> http://msdn2.microsoft.com/en-us/library/ms186243.aspx
> Hope this helps
>
> "Mike" <none@.none.com> wrote in
> news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
>> I wrote the query below, but I was wondering whether there was a
>> better way of doing so. I am basically trying to build a recursive
>> process.
>> Any suggestion would be greatly appreciated.
>> Thanks.
>> Mike
>>
>> **********************************************************************
> *
>> **********************
>> **********************************************************************
> *
>> **********************
>> CREATE PROCEDURE dbo.CustomersFiltered
>> (
>> @.filter varchar(50)
>> )
>> AS
>>
>> create table #t
>> (
>> dummyId bigint IDENTITY(1,1) PRIMARY KEY,
>> customerId bigint,
>> parentId bigint,
>> levelId int
>> )
>>
>> INSERT #t (customerId, parentId, levelId)
>> SELECT customerAutoId, parentCustomerAutoId, 0
>> FROM customers
>> WHERE [name] like @.filter
>>
>> declare @.dummyId bigint
>> select @.dummyId = 1
>>
>> declare @.customerId bigint
>> declare @.parentId bigint
>> declare @.levelId bigint
>> WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
>> BEGIN
>> select @.customerId = customerId,
>> @.parentId = parentId,
>> @.levelId = levelId
>> FROM #t
>> WHERE dummyId = @.dummyId
>> insert into #t (customerId, parentId, levelId)
>> select CustomerAutoId,
>> parentCustomerAutoId,
>> @.levelId - 1
>> from customers
>> where parentCustomerAutoId = @.customerId
>> group by CustomerAutoId,
>> parentCustomerAutoId
>> if (@.parentId is not null)
>> insert into #t (customerId, parentId, levelId)
>> select c.CustomerAutoId,
>> c.parentCustomerAutoId,
>> @.levelId + 1
>> from customers c
>> where c.customerAutoId = @.parentId
>> and not exists (select * from #t where c.customerAutoId =>> customerId)
>> group by CustomerAutoId,
>> parentCustomerAutoId
>>
>> select @.dummyId = @.dummyId + 1
>> END
>>
>> select t.levelId as LevelId,
>> t.customerId as CustomerAutoId,
>> c.[name] as [Name],
>> c.sic as SIC,
>> c.ParentCustomerAutoId as ParentCustomerAutoId
>> from #t t
>> inner join customers c on c.customerAutoId = t.customerId
>> group by t.levelId,
>> t.customerId,
>> c.[name],
>> c.sic,
>> c.ParentCustomerAutoId
>> order by levelId desc,
>> c.ParentCustomerAutoId,
>> c.[name]
>>
>> drop table #t
>> GO
> --
> Ole Kristian Bangås
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:

> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
--
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>

Query output format question

Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
353
Date
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SB
I have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>
|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:

> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>

query output format

Hey all, another question with the same SQL line:

AA$SIS$Get_superviser_name(std.superviser_number,s td.location,1)||','||

If the record is empty for that field, SQL returns "***" However, if the field is full, it returns the superviser name. The output I have is formatted for csv and the superviser name comes back as:

***
Smith, H
Johnson, P
***
Amid, D

Is it possible to strip the comma from the Supervisor name records so that it will return:

***
Smith H
Johnson P
***
Amid D

??

Any help would be appreciated!
DeeOriginally posted by psdcc
Hey all, another question with the same SQL line:

AA$SIS$Get_superviser_name(std.superviser_number,s td.location,1)||','||

If the record is empty for that field, SQL returns "***" However, if the field is full, it returns the superviser name. The output I have is formatted for csv and the superviser name comes back as:

***
Smith, H
Johnson, P
***
Amid, D

Is it possible to strip the comma from the Supervisor name records so that it will return:

***
Smith H
Johnson P
***
Amid D

??

Any help would be appreciated!
Dee

Hi dee,

Use oracle replace function for the final output string

ex : select replace(outputtext, ',',' ')

regards