I want to display the example record below in a text box without the hard
returns. How can I strip them out so the record displays in one nice line?
The hard return shows as a sqaure box in the sql table, however it does not
paste into this web form. Imagine a sqaure box at the end of each line
below...
Bilat heel and ankle pain when walking and running
Medial knee pain bilat
Anterior compartment strain bilat
Bialt hips hurt w/ occassional low back painreplace(yourTextLine, char(13), ' ')
"Chris Patten" wrote:
> I want to display the example record below in a text box without the hard
> returns. How can I strip them out so the record displays in one nice line?
> The hard return shows as a sqaure box in the sql table, however it does not
> paste into this web form. Imagine a sqaure box at the end of each line
> below...
> Bilat heel and ankle pain when walking and running
> Medial knee pain bilat
> Anterior compartment strain bilat
> Bialt hips hurt w/ occassional low back pain
Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts
Wednesday, March 28, 2012
How to Remove Carriage Returns from database field?
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
Hi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.
|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegr oups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.
|||that's great, it has solved my problem. thanks
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
Hi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.
|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegr oups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.
|||that's great, it has solved my problem. thanks
How to Remove Carriage Returns from database field?
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
How to Remove Carriage Returns from database field?
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where MyCol like '%' + char (13)
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where MyCol like '%' + char (13)
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
Monday, March 26, 2012
How to release the memory allocated to SQL Server?
The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
--
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
--
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?
Friday, March 9, 2012
How to read in multiple SELECT statements of different return types in T-SQL
HI,
I understand how to use NextResultset() in ADO.NET to return multiple
readers from a stored proc. What if a SELECT returns a scalar and
another returns rows.
Thank you!> What if a SELECT returns a scalar and
> another returns rows.
Let me rephrase above:
"What is a SELECT returns a scalar and another returns a table"
Actually all SELECT statements returns tables (with some obscure exceptions like COMPUTE). So, a
scalar is just a table that happens to have only one column and one row. I.e., you treat is as a
table. The ExecuteScalar method is just something that ADO.NET designers cooked up to make it easier
for us to get the value from a select statement that returns a table with one column and one row.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<webdevaccount@.gmail.com> wrote in message
news:1182811103.414233.319970@.m36g2000hse.googlegroups.com...
> HI,
> I understand how to use NextResultset() in ADO.NET to return multiple
> readers from a stored proc. What if a SELECT returns a scalar and
> another returns rows.
> Thank you!
>
I understand how to use NextResultset() in ADO.NET to return multiple
readers from a stored proc. What if a SELECT returns a scalar and
another returns rows.
Thank you!> What if a SELECT returns a scalar and
> another returns rows.
Let me rephrase above:
"What is a SELECT returns a scalar and another returns a table"
Actually all SELECT statements returns tables (with some obscure exceptions like COMPUTE). So, a
scalar is just a table that happens to have only one column and one row. I.e., you treat is as a
table. The ExecuteScalar method is just something that ADO.NET designers cooked up to make it easier
for us to get the value from a select statement that returns a table with one column and one row.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<webdevaccount@.gmail.com> wrote in message
news:1182811103.414233.319970@.m36g2000hse.googlegroups.com...
> HI,
> I understand how to use NextResultset() in ADO.NET to return multiple
> readers from a stored proc. What if a SELECT returns a scalar and
> another returns rows.
> Thank you!
>
Wednesday, March 7, 2012
how to read a resultset from a procedure calling a procedure
Hi all,
I'm trying to user proc1 to call proc2.
proc2 is a search proc which returns a resultset, but how can i have proc1
read that resultset without having that resultset being sent to the client?
i need to read that resultset in proc1 and then make another resultset out
of that one that will return to the client.
i was thinking temporary tables but they dont go across procedures and
##global tables dont do the trick cause they arent unique
any ideas?
thanksPedro,
Check out Erland's article at:
http://www.sommarskog.se/share_data.html
HTH
Jerry
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||You could replace Proc2 with a VIEW ?
Look up CREATE VIEW in BOL - this will give you all the information you
should need regarding Views.
HTH
Barry|||CREATE TABLE #<temptablename>
(
<colspec>
)
INSERT #<temptablename>
EXEC <storedprocedurename>|||The scope of temporary tables does extend to called stored procedures. If
you create a temp table within one stored procedure, it is visible in any
stored procedure called by that stored procedure, provided the CREATE TABLE
occurs before the EXEC. You can also create a temp table and issue
INSERT...EXEC to populate it with the result set of another stored
procedure.
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||Have you thought of using a table variable returned to proc1 from Proc2
Lookup OUTPUT in BOL
GW
"Pedro C Valenzuela" wrote:
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the client
?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>
>|||as far as i can recall, you cant use table variables for output parameters
and i havent found it in BOL either, am i wrong?
"GWilliy" <GWilliy@.discussions.microsoft.com> wrote in message
news:DB2AC26B-FCB9-45D9-ABEE-57CF97079878@.microsoft.com...
> Have you thought of using a table variable returned to proc1 from Proc2
> Lookup OUTPUT in BOL
> GW
> "Pedro C Valenzuela" wrote:
>|||On Tue, 27 Sep 2005 18:53:13 -0700, Pedro C Valenzuela wrote:
>as far as i can recall, you cant use table variables for output parameters
>and i havent found it in BOL either, am i wrong?
Hi Pedro,
No, you're right. Table variables can't be passed as input parameter to
stored procedures, nor returned as output parameter from stored
procedures. That's what table-valued user-defined functions are for.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I'm trying to user proc1 to call proc2.
proc2 is a search proc which returns a resultset, but how can i have proc1
read that resultset without having that resultset being sent to the client?
i need to read that resultset in proc1 and then make another resultset out
of that one that will return to the client.
i was thinking temporary tables but they dont go across procedures and
##global tables dont do the trick cause they arent unique
any ideas?
thanksPedro,
Check out Erland's article at:
http://www.sommarskog.se/share_data.html
HTH
Jerry
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||You could replace Proc2 with a VIEW ?
Look up CREATE VIEW in BOL - this will give you all the information you
should need regarding Views.
HTH
Barry|||CREATE TABLE #<temptablename>
(
<colspec>
)
INSERT #<temptablename>
EXEC <storedprocedurename>|||The scope of temporary tables does extend to called stored procedures. If
you create a temp table within one stored procedure, it is visible in any
stored procedure called by that stored procedure, provided the CREATE TABLE
occurs before the EXEC. You can also create a temp table and issue
INSERT...EXEC to populate it with the result set of another stored
procedure.
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||Have you thought of using a table variable returned to proc1 from Proc2
Lookup OUTPUT in BOL
GW
"Pedro C Valenzuela" wrote:
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the client
?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>
>|||as far as i can recall, you cant use table variables for output parameters
and i havent found it in BOL either, am i wrong?
"GWilliy" <GWilliy@.discussions.microsoft.com> wrote in message
news:DB2AC26B-FCB9-45D9-ABEE-57CF97079878@.microsoft.com...
> Have you thought of using a table variable returned to proc1 from Proc2
> Lookup OUTPUT in BOL
> GW
> "Pedro C Valenzuela" wrote:
>|||On Tue, 27 Sep 2005 18:53:13 -0700, Pedro C Valenzuela wrote:
>as far as i can recall, you cant use table variables for output parameters
>and i havent found it in BOL either, am i wrong?
Hi Pedro,
No, you're right. Table variables can't be passed as input parameter to
stored procedures, nor returned as output parameter from stored
procedures. That's what table-valued user-defined functions are for.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, February 24, 2012
How to query on a date range, display matching records but also display companies with no matchi
OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.
I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.
Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.
SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site
Jambi,
you can basically write an Union Sql query
SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
UNION ALL
SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE some 7 other company exist
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site
The union statement will return your date range and the other 7 company data if they exist.
Ham
How to query on a date range, display matching records but also display companies with no ma
OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.
I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.
Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.
SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site
Jambi,
you can basically write an Union Sql query
SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
UNION ALL
SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE some 7 other company exist
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site
The union statement will return your date range and the other 7 company data if they exist.
Ham
Subscribe to:
Posts (Atom)