MS SQL, Using SP in Select

Hi,
I have an SP called mTest which reads like,
Create procedure mtest
as
Begin
Select * from tbSuppliers
end.

Now I want to use the SP in a select statement like

Select * from mTest.

But it is giving me error.
Can any one give me a solution for it. (If it is possible)

Thanks in advance
PillaiOriginally posted by mbpilla
Hi,
I have an SP called mTest which reads like,
Create procedure mtest
as
Begin
Select * from tbSuppliers
end.

Now I want to use the SP in a select statement like

Select * from mTest.

But it is giving me error.
Can any one give me a solution for it. (If it is possible)

Thanks in advance
Pillai

You can not call a stored procedure in a select query but insted if u just call the stored proc as
exec mtest instead of the select query and u get the same output.|||Thanks Harshal, I got the result.

Could u please tell me the difference of trusted connection and untrusted connection.

And one more Question I got while an interview is,

What all types of connections are supported by SQL?

Thanks
Pillai|||Originally posted by mbpilla
Thanks Harshal, I got the result.

Could u please tell me the difference of trusted connection and untrusted connection.

And one more Question I got while an interview is,

What all types of connections are supported by SQL?

Thanks
Pillai
for more information refer to BOL under trusted connections.
From BOL:
A login ID only enables you to connect to an instance of SQL Server. Permissions within specific databases are controlled by user accounts. The database administrator maps your login account to a user account in any database you are authorized to access.
Instances of SQL Server must verify that the login ID supplied on each connection request is authorized to access the instance. This process is called authentication. SQL Server 2000 uses two types of authentication: Windows Authentication and SQL Server Authentication. Each has a different class of login ID.
When you connect, the SQL Server 2000 client software requests a Windows trusted connection to SQL Server 2000. Windows does not open a trusted connection unless the client has logged on successfully using a valid Windows account. The properties of a trusted connection include the Windows NT and Windows 2000 group and user accounts of the client that opened the connection. SQL Server 2000 gets the user account information from the trusted connection properties and matches them against the Windows accounts defined as valid SQL Server 2000 logins. If SQL Server 2000 finds a match, it accepts the connection. When you connect to SQL Server 2000 using Windows 2000 Authentication, your identification is your Windows NT or Windows 2000 group or user account.That is a trusted connection.|||yup it is possible to use a sproc in a select statement using open query ... or opendatasource ...
------------------------
SELECT *
FROM OPENQUERY(SvrName, 'exec sproc')
------------------------

but i believe you will have to add a linked server to your own server.

MS SQL stored procedures inside another stored procedure

Hi,

Do you know how to write stored procedures inside another stored procedure in MS SQL.

Create procedure spMyProc inputData varchar(50)

AS

-- some logical

procedure spMyProc inputInsideData varchar(10)

AS

-- some logical

-- go

---

What exactly are tou trying to do?

|||

Like Function, you can have one function inside another another function.

Function1 ()

{

Function2()

}

How about store procedure ?

spProc1

{

spProc2

}

how to write it with correct syntax?

|||

What do you mean by "having" a proc inside another proc? Execute a proc or create a proc? You can definetely call another proc but creating a proc from a proc is a very very bad idea.

|||

But, it is easy to migration. for example, currently, I have a base procedure and associated with several (5) satellite procedures, every time I have tell DBA, all of 6 proc.. I want to put into one.

|||

Hi,

First, just as ndinakar said, I also think it's not good to create a proc from a proc. You may create your procedure separately call your satellite procedures in your base procedure.

Second, if you really want to migratie all the procedures, then just put all the logic process code into one procedure, but it's also not a good way compared with the first method.

Thanks.

MS SQL Srv 2000 and extended stored procedures

hi,
I have created my own dll file that I would like to use in sql 2000 as an
extended stored procedure. I have checked the dll is written correctly (I ca
n
use it in other application). The dll file is created in C# (Visual Studio
2005).
Unfortunatelly I cannot use it in sql as the extended procedure. I am
getting error: Cannot find the function <<f_name>> in the library
<<path\library.dll>>. Reason: 127(error not found).Extended procedures need to be written in C or C++ (or Delphi, so I've heard
). A language that can
create classic DLL files, not any of the modern fancy COM dlls. Also, SQL Se
rver 2000 doesn't
support hosting any type of CLR code, whether through xp or sp_OACreate, or
whether with or without
any wrappers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
> hi,
> I have created my own dll file that I would like to use in sql 2000 as an
> extended stored procedure. I have checked the dll is written correctly (I
can
> use it in other application). The dll file is created in C# (Visual Studio
> 2005).
> Unfortunatelly I cannot use it in sql as the extended procedure. I am
> getting error: Cannot find the function <<f_name>> in the library
> <<path\library.dll>>. Reason: 127(error not found).|||Hi
This may be of interest:
"Using extended stored procedures or SP_OA stored procedures to load CLR in
SQL Server is not supported"
http://support.microsoft.com/default.aspx?scid=322884
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBcPMTWwGHA.3264@.TK2MSFTNGP03.phx.gbl...
> Extended procedures need to be written in C or C++ (or Delphi, so I've
> heard). A language that can create classic DLL files, not any of the
> modern fancy COM dlls. Also, SQL Server 2000 doesn't support hosting any
> type of CLR code, whether through xp or sp_OACreate, or whether with or
> without any wrappers.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
>|||so is it possible to create an extended stored procedure in Visual Studio
2005 (C++)?
"Michael Epprecht [MSFT]" wrote:

> Hi
> This may be of interest:
> "Using extended stored procedures or SP_OA stored procedures to load CLR i
n
> SQL Server is not supported"
> http://support.microsoft.com/default.aspx?scid=322884
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uBcPMTWwGHA.3264@.TK2MSFTNGP03.phx.gbl...
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:8F880F51-DD0E-4FC9-8887-88A805920AC9@.microsoft.com...
> so is it possible to create an extended stored procedure in Visual Studio
> 2005 (C++)?
>
Yes, but it's much easier and safer to use SQL 2005 where you can use your
C# code inside the database.
David|||Can you show me how to do that? plz
"David Browne" wrote:

> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:8F880F51-DD0E-4FC9-8887-88A805920AC9@.microsoft.com...
> Yes, but it's much easier and safer to use SQL 2005 where you can use your
> C# code inside the database.
> David
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:884D5F80-4D8F-4252-824A-2B2438330B76@.microsoft.com...
> Can you show me how to do that? plz
>
Programming SQL Server 2005 Using the .NET Framework
http://msdn.microsoft.com/sql/learn...lr/default.aspx
David

MS SQL Srv 2000 and extended stored procedures

hi,
I have created my own dll file that I would like to use in sql 2000 as an
extended stored procedure. I have checked the dll is written correctly (I can
use it in other application). The dll file is created in C# (Visual Studio
2005).
Unfortunatelly I cannot use it in sql as the extended procedure. I am
getting error: Cannot find the function <<f_name>> in the library
<<path\library.dll>>. Reason: 127(error not found).Extended procedures need to be written in C or C++ (or Delphi, so I've heard). A language that can
create classic DLL files, not any of the modern fancy COM dlls. Also, SQL Server 2000 doesn't
support hosting any type of CLR code, whether through xp or sp_OACreate, or whether with or without
any wrappers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
> hi,
> I have created my own dll file that I would like to use in sql 2000 as an
> extended stored procedure. I have checked the dll is written correctly (I can
> use it in other application). The dll file is created in C# (Visual Studio
> 2005).
> Unfortunatelly I cannot use it in sql as the extended procedure. I am
> getting error: Cannot find the function <<f_name>> in the library
> <<path\library.dll>>. Reason: 127(error not found).|||Hi
This may be of interest:
"Using extended stored procedures or SP_OA stored procedures to load CLR in
SQL Server is not supported"
http://support.microsoft.com/default.aspx?scid=322884
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBcPMTWwGHA.3264@.TK2MSFTNGP03.phx.gbl...
> Extended procedures need to be written in C or C++ (or Delphi, so I've
> heard). A language that can create classic DLL files, not any of the
> modern fancy COM dlls. Also, SQL Server 2000 doesn't support hosting any
> type of CLR code, whether through xp or sp_OACreate, or whether with or
> without any wrappers.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
>> hi,
>> I have created my own dll file that I would like to use in sql 2000 as an
>> extended stored procedure. I have checked the dll is written correctly (I
>> can
>> use it in other application). The dll file is created in C# (Visual
>> Studio
>> 2005).
>> Unfortunatelly I cannot use it in sql as the extended procedure. I am
>> getting error: Cannot find the function <<f_name>> in the library
>> <<path\library.dll>>. Reason: 127(error not found).
>|||so is it possible to create an extended stored procedure in Visual Studio
2005 (C++)?
"Michael Epprecht [MSFT]" wrote:
> Hi
> This may be of interest:
> "Using extended stored procedures or SP_OA stored procedures to load CLR in
> SQL Server is not supported"
> http://support.microsoft.com/default.aspx?scid=322884
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uBcPMTWwGHA.3264@.TK2MSFTNGP03.phx.gbl...
> > Extended procedures need to be written in C or C++ (or Delphi, so I've
> > heard). A language that can create classic DLL files, not any of the
> > modern fancy COM dlls. Also, SQL Server 2000 doesn't support hosting any
> > type of CLR code, whether through xp or sp_OACreate, or whether with or
> > without any wrappers.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Chris" <Chris@.discussions.microsoft.com> wrote in message
> > news:17231DDF-B357-4227-94E1-D94C57AB54CC@.microsoft.com...
> >> hi,
> >> I have created my own dll file that I would like to use in sql 2000 as an
> >> extended stored procedure. I have checked the dll is written correctly (I
> >> can
> >> use it in other application). The dll file is created in C# (Visual
> >> Studio
> >> 2005).
> >> Unfortunatelly I cannot use it in sql as the extended procedure. I am
> >> getting error: Cannot find the function <<f_name>> in the library
> >> <<path\library.dll>>. Reason: 127(error not found).
> >
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:8F880F51-DD0E-4FC9-8887-88A805920AC9@.microsoft.com...
> so is it possible to create an extended stored procedure in Visual Studio
> 2005 (C++)?
>
Yes, but it's much easier and safer to use SQL 2005 where you can use your
C# code inside the database.
David|||Can you show me how to do that? plz
"David Browne" wrote:
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:8F880F51-DD0E-4FC9-8887-88A805920AC9@.microsoft.com...
> > so is it possible to create an extended stored procedure in Visual Studio
> > 2005 (C++)?
> >
> Yes, but it's much easier and safer to use SQL 2005 where you can use your
> C# code inside the database.
> David
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:884D5F80-4D8F-4252-824A-2B2438330B76@.microsoft.com...
> Can you show me how to do that? plz
>
Programming SQL Server 2005 Using the .NET Framework
http://msdn.microsoft.com/sql/learning/prog/clr/default.aspx
David

MS SQL Server command function to send UDP packets from a stored procedure similar to syb_

Sorry to bug people, I searched google and the newsgroups but the
problem is that there is so much about the Slammer Worm that I was
just getting all those hits.
Is there a command or function in MS SQL Server to send a UDP packet
from a stored procedure similar to syb_sendmsg?
Something like syb_sendmsg(w.x.y.z, portnum, @.stringbuf)
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/21457;pt=5472
I have some monitoring projects and I would like to code them
similarly using sybase and MS SQL Server, I don't have much experience
with MS SQL Server but have tons with sybase. I was also not able to
find any good online command/function references, I guess I am spoiled
by the Sybase online and pdf manuals.
TIA for any help on either of those topics.Hi,
Use the procedure "sp_add_notification" to send a notification .The details
and usage you can get from books online.
You could install the SQL server 2000 books online. DOwnload the books
online form below link:-
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Could you install the latest service pack in your server to secure your sql
server from Slammer. Download and install the
sp3a from below link.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
Thanks
Hari
MCDBA
"forsale" <google.20.webinfo@.xoxy.net> wrote in message
news:b754dde0.0407251800.20129d4d@.posting.google.com...
> Sorry to bug people, I searched google and the newsgroups but the
> problem is that there is so much about the Slammer Worm that I was
> just getting all those hits.
> Is there a command or function in MS SQL Server to send a UDP packet
> from a stored procedure similar to syb_sendmsg?
> Something like syb_sendmsg(w.x.y.z, portnum, @.stringbuf)
>
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__Boo
kTextView/21457;pt=5472
> I have some monitoring projects and I would like to code them
> similarly using sybase and MS SQL Server, I don't have much experience
> with MS SQL Server but have tons with sybase. I was also not able to
> find any good online command/function references, I guess I am spoiled
> by the Sybase online and pdf manuals.
> TIA for any help on either of those topics.|||Thanks, that might almost work, but I was really looking for something to send UDP.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<#T69eEscEHA.1356@.TK2MSFTNGP09.phx.gbl>...
> Hi,
>
> Use the procedure "sp_add_notification" to send a notification .The details
> and usage you can get from books online.

MS SQL server 2005: collect procedure for "dts pipeline generate error

Dear experts,

My MS SQL Server 2005 is generating the following error. may i know what's wrong with it?

"
The Collect Procedure for the "DTSPipeline" service in DLL "XXX:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTSPipelinePerf.dll" generated an exception or returned an invalid status. Performance data returned by counter DLL will be not be returned in Perf Data Block. The exception or status code returned is the first DWORD in the attached data.
"

Thanks in advance for any assistance rendered.
pat

Where are you seeing this error? During install or when you run something?

Thanks,
Sam Lester (MSFT)

|||Hi Sam,

It is an error generated in the application log. source: Perflib with an EventID [1010]. The server that generated the error when it was running.

Hopefully, my limited information helps. Thanks for attending to my problem!

Cheers,
Patrick|||

Patrick, I'm moving it over to the IS thread to see if they know the error.

Thanks,
Sam

|||Hi Sam,

Thanks for the effort. However, I have not received any comments about the error. Maybe you can refer me to other fourms where I can find leads to this issue?

Thanks,
Patrick|||Are you able to create and execute any ssis package on this server?|||I am also seeing this error in my Event Viewer on a Windows 2003 Server with SQL 2005 installed, as well as IIS.

I have tried the following "perflib fix" I found on the web, but it does not effect this error message and it is still showing up.

fix for perflib errors in event logs on WinServer2k & WinServer2k3
open a cmd prompt, change working directory to
C:\windows\system32 and type the following:
unlodctr w3svc
unlodctr msftpsvc
unlodctr asp
unlodctr inetinfo
lodctr w3ctrs.ini
lodctr ftpctrs.ini
lodctr axperf.ini
lodctr infoctrs.ini
reboot when done

Anyone have an idea on this one?

MS SQL SERVER 2005 BUG? Cannot insert NULL into column diagram_id

Hi friends,
when trying to save a diagram I got an error:
The sp_creatediagram procedure attempted to return a status of NULL, which is not allowed.
Whats with this??I had the same issue an just fixed it by turning the "diagram_id" field in SysDiagrams table to "identitity".

I dropped the table and ran the following script.

CREATE TABLE [dbo].[sysdiagrams](
[name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[principal_id] [int] NOT NULL,
[diagram_id] [int] identity(1,1),
[version] [int] NULL,
[definition] [varbinary](max) NULL
) ON [PRIMARY]

It worked.

MS SQL Joins

Hi Folks,
I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:

Store Procedure:

--Create Procedure dbo.IMS_Donation

--AS

Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
Into Donor_Visit1
From DNR_VST_DB_REC
Where D_VST_DATE Between 20010101 AND 20040512
AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
AND D_VST_STATUS = 'DN'
ORDER BY D_VST_ID
GO

SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
count(*) as 'COUNT_VISITS'
INTO Donor_Visit2
FROM DNR_VST_DB_REC, Donor_Visit1
Where D_VST_ID = DRWLOC_ID
AND NOT EXISTS (Select R_DCC_ID
From REC_DCC_DB_REC
Where R_DCC_ID = DRWLOC_ID
AND R_DCC_INSTID = DRWLOC_INSTID
AND R_DCC_CALLCD = 'DC')
GROUP BY DRWLOC_ID, DRWLOC_INSTID
GO

SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
INTO Donor_Visit3
FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
AND VST.D_VST_ID = DVT1.DRWLOC_ID
AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_STATCD != 'MOD'
AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
AND VST.D_VST_STATUS = 'DN'
GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID

GO

SELECT DISTINCT
NAM.N_NAM_ID AS 'ID1',
NAM.N_NAM_INSTID AS 'INSTID1',
NAM.N_NAM_FNAME AS 'FNAME1',
NAM.N_NAM_MINITIAL AS 'MINITIAL1',
NAM.N_NAM_LNAME AS 'LNAME1',
PER.N_PER_BIRTH AS 'BIRTH1',
ADR.N_ADR_ADDR1 AS 'ADDR1',
ADR.N_ADR_ADDR2 AS 'ADDR2',
ADR.N_ADR_CITY AS 'CITY1',
ADR.N_ADR_STATE AS 'STATE1',
SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
PER.N_PER_EMAIL AS 'EMAIL1',
PER.N_PER_GENDER AS 'GENDER1',
PHNA.N_PHN_AREACD AS 'AREAD1',
PHNA.N_PHN_PREFIX AS 'PREFIXD1',
PHNA.N_PHN_NUMBER AS 'NBRD1',
PHNA.N_PHN_EXTENTN AS 'EXTD1',
PHNB.N_PHN_AREACD AS 'AREAD2',
PHNB.N_PHN_PREFIX AS 'PREFIXD2',
PHNB.N_PHN_NUMBER AS 'NBRE2',
PHNB.N_PHN_EXTENTN AS 'EXTD2',
BTY.D_BTY_ABO AS 'ABO1',
BTY.D_BTY_RHESUS AS 'RHI',
VST.D_VST_DATE AS 'FIRST1',
DV2.COUNT_VISITS AS 'COUNT',
SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
ELG.D_ELG_RWBDTE AS 'ELIG1'
--INTO Donor_Visit4
From Donor_Visit2 DV2
RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
RIGHT OUTER JOIN NAT_PER_DB_REC PER
ON DV2.COUNT_INSTID = PER.N_PER_INSTID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
ON DV2.COUNT_ID = DNT.D_DTS_ID
RIGHT OUTER JOIN NAT_PER_DB_REC PER1
ON DV2.COUNT_ID = PER1.N_PER_ID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
ON DV2.COUNT_ID = BTY1.D_BTY_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
ON DV2.COUNT_ID = PHNA1.N_PHN_ID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
ON DV2.COUNT_ID = PHNB1.N_PHN_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
ON PHNA2.N_PHN_PHTYP = 'D'
LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
ON PHNB2.N_PHN_PHTYP = 'E',
--LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
--DTS1.D_DTS_CNTTYP <> 'N',
DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
AND DV2.COUNT_INSTID = N_NAM_INSTID
AND DV2.COUNT_INSTID = N_ADR_INSTID
AND DV2.COUNT_INSTID = VST.D_VST_INSTID
--AND DV2.COUNT_INSTID = ELG.D_ELG_ID
AND NAM.N_NAM_SEQNO = 0
AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
FROM DNR_VST_DB_REC VSTB
WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
AND VSTB.D_VST_STATUS = 'DN'
AND VST.D_VST_ID = VSTB.D_VST_ID)
AND NOT EXISTS (SELECT R_DRC_ID
FROM REC_DRC_DB_REC
WHERE R_DRC_ID = COUNT_ID
AND R_DRC_INSTID = COUNT_INSTID
AND R_DRC_RESPCD = '15')
GROUP BY
NAM.N_NAM_ID,
NAM.N_NAM_INSTID,
NAM.N_NAM_FNAME,
NAM.N_NAM_MINITIAL,
NAM.N_NAM_LNAME,
PER.N_PER_BIRTH,
ADR.N_ADR_ADDR1,
ADR.N_ADR_ADDR2,
ADR.N_ADR_CITY,
ADR.N_ADR_STATE,
ADR.N_ADR_ZIP,
PER.N_PER_EMAIL,
PER.N_PER_GENDER,
PHNA.N_PHN_AREACD,
PHNA.N_PHN_PREFIX,
PHNA.N_PHN_NUMBER,
PHNA.N_PHN_EXTENTN,
PHNB.N_PHN_AREACD,
PHNB.N_PHN_PREFIX,
PHNB.N_PHN_NUMBER,
PHNB.N_PHN_EXTENTN,
BTY.D_BTY_ABO,
BTY.D_BTY_RHESUS,
VST.D_VST_DATE,
DV2.COUNT_VISITS,
DTS.D_DTS_DONSUM,
ELG.D_ELG_RWBDTE

Error Message:

(845 row(s) affected)

(844 row(s) affected)

(396 row(s) affected)

Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 2
Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.Ok, its a hog.

First, see if you can "blow out" tempdb using DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp).

If that doesn't help enough, see if you can create an index that the GROUP BY expression can use... It is often enough to get the first three or four columns covered, since that can buy you an enormous reduction in staging space.

If that doesn't help, buy more disk!

-PatP|||My God, I mean Oh Codd, you have 27 GROUP BY's!!!!... You realize that your tempdb would be the bottleneck throughout the life of your app! Are you sure you need all 27?.. Click on estimated execution plan icon in QA and see what you get there.

MS SQL Dynamic stored procedure using a datetime variable

Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.

What I have is:

CREATE PROCEDURE busy_report

@.TableName varchar(255),
@.reporteddate datetime=NULL

AS
if @.reporteddate is null
select @.reporteddate = CURRENT_TIMESTAMP

-- Create a variable @.SQLStatement
DECLARE @.SQLStatement varchar(255)
SET DATEFORMAT dmy

-- Enter the dynamic SQL statement into the
-- variable @.SQLStatement
SELECT @.SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@.TableName + "WHERE reporteddate = ' "
+ @.reporteddate + " '"

-- Execute the SQL statement
EXEC(@.SQLStatement)
GO

The error I keep getting is:

Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.

Any ideas appreciated.

(Edit:)

I've also tried it this way:

CREATE PROCEDURE UK_busy_report

@.TableName varchar(255),
@.reporteddate datetime=NULL

AS

-- Create a variable @.SQLStatement
DECLARE @.SQLStatement varchar(255)
SELECT @.reporteddate=CONVERT(datetime, @.reporteddate)
IF @.@.ERROR <> 0 BEGIN

/* Do some error processing */

PRINT 'Error Occured' END

ELSE
-- Enter the dynamic SQL statement into the
-- variable @.SQLStatement
SELECT @.SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@.TableName + "WHERE reporteddate = ' "
+ @.reporteddate + " '"

-- Execute the SQL statement
EXEC(@.SQLStatement)
GO

Which gives me the same error!

.logic.Ahhh...the smell of Oracle....

CREATE PROCEDURE busy_report
@.TableName varchar(255)
, @.reporteddate datetime=NULL
AS
BEGIN
DECLARE @.SQLStatement varchar(255)

IF @.reporteddate IS NULL
SELECT @.reporteddate = GetDate()

SELECT @.SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM '
+ @.TableName + 'WHERE reporteddate = '
+ ''''
+ @.reporteddate
+ ''''

EXEC(@.SQLStatement)
END
GO

It's been a while

If you want specifc date formats look up CONVERT in Books Online (BOL)|||It gives you the same error because it occurs on the last concatenation of @.SQLStatement.

"WHERE reporteddate = '" + convert(char(10), @.reporteddate, 101) + "'"|||Originally posted by rdjabarov
It gives you the same error because it occurs on the last concatenation of @.SQLStatement.

"WHERE reporteddate = '" + convert(char(10), @.reporteddate, 101) + "'"

Yeah...you're definetley going to need to worry about conversion...

What's the column reporteddate defined as?|||Originally posted by Brett Kaiser
Yeah...you're definetley going to need to worry about conversion...

What's the column reporteddate defined as?

Hi, the column is a smalldatetime type.

With your solution Brett I' getting a Server: Msg 295, Level 16, State 3, Procedure UK_busy_report, Line 11
Syntax error converting character string to smalldatetime data type.

Using:

CREATE PROCEDURE UK_busy_report
@.TableName varchar(255)
, @.reporteddate smalldatetime=NULL
AS
BEGIN
DECLARE @.SQLStatement varchar(255)

IF @.reporteddate IS NULL
SELECT @.reporteddate = GetDate()

SELECT @.SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM '
+ @.TableName + 'WHERE reporteddate = '
+ ''''
+ @.reporteddate
+ ''''

EXEC(@.SQLStatement)
END
GO

I've also tried to convert it to nvarchar as follows:

CREATE PROCEDURE UK_busy_report
@.TableName varchar(255)
, @.reporteddate smalldatetime=NULL
AS
BEGIN
DECLARE @.SQLStatement varchar(255)

IF @.reporteddate IS NULL
SELECT @.reporteddate = GetDate()

SELECT @.SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM '
+ @.TableName + 'WHERE reporteddate = '
+ ''''
+ convert (nvarchar(14), @.reporteddate, 101)
+ ''''

EXEC(@.SQLStatement)
END
GO

And I get: Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

I think this conversion is the way to go but not sure of exact syntax. I'll keep checking through BOL and if anyone has any more ideas they'd be greatly appreciated :)

.logic.|||I've again altered it to:

CREATE PROCEDURE UK_busy_report
@.TableName varchar(255)
, @.reporteddate varchar(40)
AS
BEGIN
DECLARE @.SQLStatement varchar(255)
DECLARE @.date datetime


SELECT @.reporteddate=CONVERT(datetime, @.date, 103)
IF @.@.ERROR <> 0

BEGIN
Print 'ERROR'
END

ELSE

SELECT @.SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM '
+ @.TableName + 'WHERE reporteddate = '
+ ''''
+ convert (nvarchar(14), @.reporteddate, 101)
+ ''''

EXEC(@.SQLStatement)
END
GO

I'm running it with: exec UK_busy_report EU_master_week6, '02/02/04';

And it seems to be running fine but it doesn't return any info, even though I know that date exists in the table.

MS SQL 6.5 Procedure to Send Query results via Email

Hi,
I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process which sends an email reminder to
each customer in the database, that has outstanding orders. This email
reminder should have the results of the query regarding their account.
The table structure are as follows.
Customer_tbl
CustomerID
AccountNo
Name
EmailAddress
Order_tbl
OrderID
CustomerID
Reference
Amount
Date
Outstanding_flg
Can anyone help?
Sen.
Hi
As discussed the below steps were performed:-
You can configure a SQL Mail account to do this and your SQl Server and SQL
Executive service
should be started using a domain account which have previlages to the mail
server.
Also you should have a mail profile configured in your SQl Server machine.
After that
you can use the Extended procedure XPS_ATRTMAIL to start mail session and
xp_sendmail
to fire a Select statement and send the result as a mal to receiver(s).
Below sample will send the output of sysobjects to
xp_sendmail @.recipients = 'hari_prasad_k@.hotmail.com',
x@.query = 'select * from sysobjects',
@.subject = 'SQL Server Report',
@.message = 'The contents of sysobjects:',
@.attach_results = 'TRUE', @.width = 250
Please have a look into books online (SQL 6.5) for below procedures to
configure mail.
xp_startmail
xp_sendmail
sp_processmail
xp_readmail
xp_deletemail
xp_stopmail
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"serendipity" <abc@.hotmail.com> wrote in message
news:40e12aae$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> --
> Customer_tbl
> --
> CustomerID
> AccountNo
> Name
> EmailAddress
> --
> Order_tbl
> --
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.
>
|||Hi
As discussed the below steps were performed:-
You can configure a SQL Mail account to do this and your SQl Server and SQL
Executive service
should be started using a domain account which have previlages to the mail
server.
Also you should have a mail profile configured in your SQl Server machine.
After that
you can use the Extended procedure XPS_ATRTMAIL to start mail session and
xp_sendmail
to fire a Select statement and send the result as a mal to receiver(s).
Below sample will send the output of sysobjects to
xp_sendmail @.recipients = 'hari_prasad_k@.hotmail.com',
x@.query = 'select * from sysobjects',
@.subject = 'SQL Server Report',
@.message = 'The contents of sysobjects:',
@.attach_results = 'TRUE', @.width = 250
Please have a look into books online (SQL 6.5) for below procedures to
configure mail.
xp_startmail
xp_sendmail
sp_processmail
xp_readmail
xp_deletemail
xp_stopmail
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"serendipity" <abc@.hotmail.com> wrote in message
news:40e12aae$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> --
> Customer_tbl
> --
> CustomerID
> AccountNo
> Name
> EmailAddress
> --
> Order_tbl
> --
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.
>

MS SQL 6.5 Procedure to Send Query results via Email

Hi,

I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process which sends an email reminder to
each customer in the database, that has outstanding orders. This email
reminder should have the results of the query regarding their account.

The table structure are as follows.

-------
Customer_tbl
-------
CustomerID
AccountNo
Name
EmailAddress

-------
Order_tbl
-------
OrderID
CustomerID
Reference
Amount
Date
Outstanding_flg

Can anyone help?
Sen.Hi

Posting DDL (Create table statements etc) and example data (as insert
statements) will remove any ambiguity when people try to answer your
questions.

To send individual emails you will need to use a cursor to send separate
emails to each customer. Check out xp_sendmail in Books online for details
on how to send emails from SQL Server.

You will have to be careful when configuring SQL Mail on SQLServer 6.5 and
the version of outlook you use.

INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231

HTH

John

"serendipity" <abc@.hotmail.com> wrote in message
news:40e12993$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> -------
> Customer_tbl
> -------
> CustomerID
> AccountNo
> Name
> EmailAddress
> -------
> Order_tbl
> -------
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.|||Sen,

If you have a machine with the .NET Framework that can connect to the
SQL Server, give Gadami a try
(http://www.kripsoft.com/pages/2/index.htm). Insert your database
connection information and query in an XML file (see examples shipped
with product), and run GadamiConfig.exe to send the emails out. It
has a 'burst' feature that will automatically send each set of orders
to each customer. Merely tell the program what column in the query
contains your email addresses and it will split the data and send out
emails with HTML tables of the data.

The product is free to use for sending out up to 100 emails at a time,
and has a nominal cost if you want to send out more.

-Krip

MS SQL 6.5 Procedure to Send Query results via Email

Hi,
I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process which sends an email reminder to
each customer in the database, that has outstanding orders. This email
reminder should have the results of the query regarding their account.
The table structure are as follows.
--
Customer_tbl
--
CustomerID
AccountNo
Name
EmailAddress
--
Order_tbl
--
OrderID
CustomerID
Reference
Amount
Date
Outstanding_flg
Can anyone help?
Sen.Hi
As discussed the below steps were performed:-
You can configure a SQL Mail account to do this and your SQl Server and SQL
Executive service
should be started using a domain account which have previlages to the mail
server.
Also you should have a mail profile configured in your SQl Server machine.
After that
you can use the Extended procedure XPS_ATRTMAIL to start mail session and
xp_sendmail
to fire a Select statement and send the result as a mal to receiver(s).
Below sample will send the output of sysobjects to
xp_sendmail @.recipients = 'hari_prasad_k@.hotmail.com',
x@.query = 'select * from sysobjects',
@.subject = 'SQL Server Report',
@.message = 'The contents of sysobjects:',
@.attach_results = 'TRUE', @.width = 250
Please have a look into books online (SQL 6.5) for below procedures to
configure mail.
xp_startmail
xp_sendmail
sp_processmail
xp_readmail
xp_deletemail
xp_stopmail
Thanks
Hari
MCDBA
--
Thanks
Hari
MCDBA
"serendipity" <abc@.hotmail.com> wrote in message
news:40e12aae$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> --
> Customer_tbl
> --
> CustomerID
> AccountNo
> Name
> EmailAddress
> --
> Order_tbl
> --
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.
>

MS SQL 6.5 Procedure to Send Query results via Email

Hi,
I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process which sends an email reminder to
each customer in the database, that has outstanding orders. This email
reminder should have the results of the query regarding their account.
The table structure are as follows.
Customer_tbl
--
CustomerID
AccountNo
Name
EmailAddress
Order_tbl
--
OrderID
CustomerID
Reference
Amount
Date
Outstanding_flg
Can anyone help?
Sen.Hi
As discussed the below steps were performed:-
You can configure a SQL Mail account to do this and your SQl Server and SQL
Executive service
should be started using a domain account which have previlages to the mail
server.
Also you should have a mail profile configured in your SQl Server machine.
After that
you can use the Extended procedure XPS_ATRTMAIL to start mail session and
xp_sendmail
to fire a Select statement and send the result as a mal to receiver(s).
Below sample will send the output of sysobjects to
xp_sendmail @.recipients = 'hari_prasad_k@.hotmail.com',
x@.query = 'select * from sysobjects',
@.subject = 'SQL Server Report',
@.message = 'The contents of sysobjects:',
@.attach_results = 'TRUE', @.width = 250
Please have a look into books online (SQL 6.5) for below procedures to
configure mail.
xp_startmail
xp_sendmail
sp_processmail
xp_readmail
xp_deletemail
xp_stopmail
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"serendipity" <abc@.hotmail.com> wrote in message
news:40e12aae$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> --
> Customer_tbl
> --
> CustomerID
> AccountNo
> Name
> EmailAddress
> --
> Order_tbl
> --
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.
>

MS SQL 2000 Query Help

I have patient data - am interested in patients receiving a certain
diagnostic procedure. Need to find out on an average in the group of patient
s
I am analyzing, on which visit they receive this procedure.
Suppose there is a consumer X, who visits doc A - on the 5th visit the doc
administers this diagnostic test and another doc could administer this test
to a different consumer on the second visit - need to know overall the visit
in which the patients get this procedure.
Can anybody suggest a way to accomplish this..
Thanks"SJ" <SJ@.discussions.microsoft.com> wrote in message
news:9F11FE30-FF4D-4B63-BF03-8FD6072442E5@.microsoft.com...
>I have patient data - am interested in patients receiving a certain
> diagnostic procedure. Need to find out on an average in the group of
> patients
> I am analyzing, on which visit they receive this procedure.
> Suppose there is a consumer X, who visits doc A - on the 5th visit the doc
> administers this diagnostic test and another doc could administer this
> test
> to a different consumer on the second visit - need to know overall the
> visit
> in which the patients get this procedure.
> Can anybody suggest a way to accomplish this..
> Thanks
How are we supposed to guess what your data, tables, keys, constraints, etc
look like? Try posting again after you've read the advice in the following
article. I'm sure someone can help you out if you include more information.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||CREATE TABLE #temp
(patient_id INT NULL,
doc_id CHAR(9) NULL,
serv_date DATETIME NULL,
proc_code CHAR(6) NULL)
The patient id is unique to each patient.
acs_id prov_id serv_date
proc_code
2001093 000175900 2005-10-03 00:00:00.000 90853
1929596 100175901 2005-10-03 00:00:00.000 90806
1443313 000175902 2005-09-29 00:00:00.000 90806
2001093 000175900 2005-10-05 00:00:00.000 90801
1929596 100175901 2005-10-07 00:00:00.000 90806
1929596 100175901 2005-10-10 00:00:00.000 90801
1443313 000175902 2005-10-29 00:00:00.000 90805
1443313 000175902 2005-10-29 00:00:00.000 90801
I am trying to get on an average the visit (first/second/third) during which
they get the 90801 proc_code?
Hope this is clear.
Thanks
"David Portas" wrote:

> "SJ" <SJ@.discussions.microsoft.com> wrote in message
> news:9F11FE30-FF4D-4B63-BF03-8FD6072442E5@.microsoft.com...
> How are we supposed to guess what your data, tables, keys, constraints, et
c
> look like? Try posting again after you've read the advice in the following
> article. I'm sure someone can help you out if you include more information
.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>|||On Thu, 1 Dec 2005 13:44:02 -0800, "SJ" <SJ@.discussions.microsoft.com>
wrote:
>CREATE TABLE #temp
>(patient_id INT NULL,
> doc_id CHAR(9) NULL,
> serv_date DATETIME NULL,
> proc_code CHAR(6) NULL)
>The patient id is unique to each patient.
>acs_id prov_id serv_date
> proc_code
>2001093 000175900 2005-10-03 00:00:00.000 90853
>1929596 100175901 2005-10-03 00:00:00.000 90806
>1443313 000175902 2005-09-29 00:00:00.000 90806
>2001093 000175900 2005-10-05 00:00:00.000 90801
>1929596 100175901 2005-10-07 00:00:00.000 90806
>1929596 100175901 2005-10-10 00:00:00.000 90801
>1443313 000175902 2005-10-29 00:00:00.000 90805
>1443313 000175902 2005-10-29 00:00:00.000 90801
>I am trying to get on an average the visit (first/second/third) during whic
h
>they get the 90801 proc_code?
>Hope this is clear.
>Thanks
It's clear.
Very common problem, but "n'th" is a tough topic in relational.
The approximate solution is to insert these records, sorted, into a
new (temp) table that also has an indentity key. Take the max(key) -
min(key) for each patient, that gives you the n'th!
Hope that's enough of a hint ...
Josh|||CREATE TABLE #temp
(patient_id INT NULL,
doc_id CHAR(9) NULL,
serv_date DATETIME NULL,
proc_code CHAR(6) NULL)
go
insert #temp(patient_id, doc_id, serv_date, proc_code )
values(2001093,'000175900','2005-10-03 00:00:00.000','90853')
insert #temp(patient_id, doc_id, serv_date, proc_code )
values(1929596,'100175901','2005-10-03 00:00:00.000','90806')
insert #temp(patient_id, doc_id, serv_date, proc_code )
values(1443313,'000175902','2005-09-29 00:00:00.000','90806')
insert #temp(patient_id, doc_id, serv_date, proc_code )
values(2001093,'000175900','2005-10-05 00:00:00.000','90801')
insert #temp(patient_id, doc_id, serv_date, proc_code )
values(1929596,'100175901','2005-10-07 00:00:00.000','90806')
insert #temp(patient_id, doc_id, serv_date, proc_code )
values(1929596,'100175901','2005-10-10 00:00:00.000','90801')
insert #temp(patient_id, doc_id, serv_date, proc_code )
values(1443313,'000175902','2005-10-29 00:00:00.000','90805')
insert #temp(patient_id, doc_id, serv_date, proc_code )
values(1443313,'000175902','2005-10-29 00:00:00.000','90801')
select avg(cnt)
from (
select t1.patient_id, count(*) as cnt
from #temp t1
where t1.serv_date<=(
select min(t2.serv_date)
from #temp t2
where t2.patient_id=t1.patient_id and t2.proc_code='90801')
group by t1.patient_id) t
dean
"SJ" <SJ@.discussions.microsoft.com> wrote in message
news:B85C4F8E-48A7-4C70-A018-3E22BC3D0732@.microsoft.com...
> CREATE TABLE #temp
> (patient_id INT NULL,
> doc_id CHAR(9) NULL,
> serv_date DATETIME NULL,
> proc_code CHAR(6) NULL)
> The patient id is unique to each patient.
> acs_id prov_id serv_date
> proc_code
> 2001093 000175900 2005-10-03 00:00:00.000 90853
> 1929596 100175901 2005-10-03 00:00:00.000 90806
> 1443313 000175902 2005-09-29 00:00:00.000 90806
> 2001093 000175900 2005-10-05 00:00:00.000 90801
> 1929596 100175901 2005-10-07 00:00:00.000 90806
> 1929596 100175901 2005-10-10 00:00:00.000 90801
> 1443313 000175902 2005-10-29 00:00:00.000 90805
> 1443313 000175902 2005-10-29 00:00:00.000 90801
> I am trying to get on an average the visit (first/second/third) during
> which
> they get the 90801 proc_code?
> Hope this is clear.
> Thanks
>
> "David Portas" wrote:
>|||Thank You - this is .
"Dean" wrote:

> CREATE TABLE #temp
> (patient_id INT NULL,
> doc_id CHAR(9) NULL,
> serv_date DATETIME NULL,
> proc_code CHAR(6) NULL)
> go
>
> insert #temp(patient_id, doc_id, serv_date, proc_code )
> values(2001093,'000175900','2005-10-03 00:00:00.000','90853')
> insert #temp(patient_id, doc_id, serv_date, proc_code )
> values(1929596,'100175901','2005-10-03 00:00:00.000','90806')
> insert #temp(patient_id, doc_id, serv_date, proc_code )
> values(1443313,'000175902','2005-09-29 00:00:00.000','90806')
> insert #temp(patient_id, doc_id, serv_date, proc_code )
> values(2001093,'000175900','2005-10-05 00:00:00.000','90801')
> insert #temp(patient_id, doc_id, serv_date, proc_code )
> values(1929596,'100175901','2005-10-07 00:00:00.000','90806')
> insert #temp(patient_id, doc_id, serv_date, proc_code )
> values(1929596,'100175901','2005-10-10 00:00:00.000','90801')
> insert #temp(patient_id, doc_id, serv_date, proc_code )
> values(1443313,'000175902','2005-10-29 00:00:00.000','90805')
> insert #temp(patient_id, doc_id, serv_date, proc_code )
> values(1443313,'000175902','2005-10-29 00:00:00.000','90801')
>
> select avg(cnt)
> from (
> select t1.patient_id, count(*) as cnt
> from #temp t1
> where t1.serv_date<=(
> select min(t2.serv_date)
> from #temp t2
> where t2.patient_id=t1.patient_id and t2.proc_code='90801')
> group by t1.patient_id) t
> dean
> "SJ" <SJ@.discussions.microsoft.com> wrote in message
> news:B85C4F8E-48A7-4C70-A018-3E22BC3D0732@.microsoft.com...
>
>

Ms Sql

Can anybody help me about this??

I'm new from using the MS SQL and i need a function/procedure/trigger......

there is this function from Delphi (FormatCurr) that I used to create a new record id
...FormatCurr('0000000000',Tqry.FieldByName('Budg etNo').AsInteger+1........

but then i want to make the same thing in MS SQL but i dont know what function to used so instead i did this dumb coding thing

CREATE PROCEDURE budget_newrecord
AS
DECLARE @.budgetno_old int,@.budgetno_new CHAR(10), @.budget_no int
SELECT TOP 1 @.budgetno_old = cast(budgetno as int)from budgetinfo order by budgetno desc

SET @.budget_no = @.budgetno_old + 1

IF @.budget_no <= 0
SET @.budgetno_new = '0000000001'
ELSE IF @.budget_no < 10
SET @.budgetno_new = '000000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100
SET @.budgetno_new = '00000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000
SET @.budgetno_new = '0000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000
SET @.budgetno_new = '000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000
SET @.budgetno_new = '00000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000
SET @.budgetno_new = '0000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000000
SET @.budgetno_new = '000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '00' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '0' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000000
SET @.budgetno_new = cast(@.budget_no as char)

PRINT @.budgetno_new
RETURN @.budgetno_new
GO

Quote:

Originally Posted by clear1140

Can anybody help me about this??

I'm new from using the MS SQL and i need a function/procedure/trigger......

there is this function from Delphi (FormatCurr) that I used to create a new record id
...FormatCurr('0000000000',Tqry.FieldByName('Budg etNo').AsInteger+1........

but then i want to make the same thing in MS SQL but i dont know what function to used so instead i did this dumb coding thing

CREATE PROCEDURE budget_newrecord
AS
DECLARE @.budgetno_old int,@.budgetno_new CHAR(10), @.budget_no int
SELECT TOP 1 @.budgetno_old = cast(budgetno as int)from budgetinfo order by budgetno desc

SET @.budget_no = @.budgetno_old + 1

IF @.budget_no <= 0
SET @.budgetno_new = '0000000001'
ELSE IF @.budget_no < 10
SET @.budgetno_new = '000000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100
SET @.budgetno_new = '00000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000
SET @.budgetno_new = '0000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000
SET @.budgetno_new = '000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000
SET @.budgetno_new = '00000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000
SET @.budgetno_new = '0000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000000
SET @.budgetno_new = '000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '00' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '0' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000000
SET @.budgetno_new = cast(@.budget_no as char)

PRINT @.budgetno_new
RETURN @.budgetno_new
GO

try:

@.budgetno_new = right('000000000'+ rtrim(ltrim(cast(@.budget_no as varchar(12))),9)|||

Quote:

Originally Posted by ck9663

try:

@.budgetno_new = right('000000000'+ rtrim(ltrim(cast(@.budget_no as varchar(12))),9)

Hey ck9663....... thanks a lot...... it really works.......... thank you!!!!!!!!!!!