Getting a stored procedures return value -- URGENT !

We've got some code that has been using a SqlCommand with the commandtype
set to Text. It is running a stored procedure by building a StringBuilder
object to string together the parameters and then execute. The problem I am
running into is that if I add a parameter to the commands paramter
collection and designate it as the return value in the "direction"
parameter, I never get the value returned.
I'm assuming it is because when executing a stored proc in this manner
(instead of using commandtype of StoredProcedure) that the stored procedure
is actually considered to be nested within the "procedural" code I'm
executing as text. Does this make sense? I hope that explanation is clear
enough. I really need to be able to access these return codes without
rewriting the world. As it is now they have all their stored procs doing a
"select ##" to send a return code back to their C# code. This is ludicrous
and I cannot reuse any of these storedprocs from another stored proc. I
don't see anyway to get the select results of a nested stored proc...
I'm on a tight deadline here haven't much time to solve this before writing
it over would be faster.
Any help is greatly appreciated!Hi
Did you check out:
http://msdn.microsoft.com/library/d...r />
outas.asp
The return values are only available once all result sets have been
processed.
John
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>|||> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
If you must stick with CommandType.Text for now, you might try passing the
return code value as an output parameter value. At least that will lessen
the immediate code changes needed.
As you probably know, it's generally a bad technique to build literal
strings instead of using parameterized procs and queries. When you get
around to converting to CommandType.StoredProcedure, ditch the StringBuilder
and use input parameters instead as well as the proper return value
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>

Getting a return value from a Stored Procedure

Hi all,
Is there anyway to get a returned value from a called Stored Procedure from within a piece ofSQL? For example, I have the following code...

DECLARE @.testval AS INT
SET @.testval = EXEC u_checknew_dwi_limits '163'
IF (@.testval = 0)
BEGIN
PRINT '0 Returned'
END
ELSE
BEGIN
PRINT '1 Returned'
END
...whichas you can see calls a SP called 'u_checknew_dwi_limits'. This SP(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want toassign that value to the '@.testval' variable (as you can see in mycode) - but Query Analyser is throwing an error at me. Is this thecorrect way to do this?
Thanks
Tryst

The SP that you are calling should contain an OUTPUT parameter.
So, in your big SP you would get the OUTPUT parameter as follows:
DECLARE @.outParm VARCHAR(50)
EXEC SP_Name , ...(input parameters), ... @.outParam (output parameter)
print @.outParam
Hope that helps ,
Regards

|||Hi, and thanks for the reply. Its seems I got what I needed from using the following line of code...
DECLARE @.testval AS INT
EXEC @.testval = u_checknew_dwi_limits @.varval
Is this a more efficient way of doing thing?
Tryst

Getting a Return value from a Function.

Im a self proclaimed newb and Im stuck on returning a value from a function. I want to get the AttendID that the SQL statement returns and dump it into strAttendID:Response.Redirect("ClassSurvey.aspx?Pupil=" & strAttendID)
I cant seem to accomplish this. It returns nothing. Please help.
TIA,
Stue
<code>
Function Get_AttendID(ByVal strAttendIDAsString)As SqlDataReader
Dim connStringAsString = ConfigurationSettings.AppSettings("ClassDB")
Dim sqlConnAsNew SqlConnection(connString)
Dim sqlCmdAs SqlCommand
Dim drAs SqlDataReader

sqlConn.Open()
Dim strSQLAsString = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'"

sqlCmd =New SqlCommand(strSQL, sqlConn)

sqlCmd.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50)
sqlCmd.Parameters("@.FirstName").Value = tbFirstName.Text
sqlCmd.Parameters.Add("@.LastName", SqlDbType.VarChar, 50)
sqlCmd.Parameters("@.LastName").Value = tbLastName.Text
sqlCmd.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8)
sqlCmd.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString
dr = sqlCmd.ExecuteReader()
dr.Close()
sqlConn.Close()

Return dr

EndFunction
</code>

Why are you returning a datareader if all you want is the attend id and why are you even using the datareader at all when all you are looking for is one value.
The best way would be to use executescalar method and return the value. excuse the sample code because it is C#

publicstring AttendID()
{
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings("ClassDB"));
string strSQL = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'";
SqlCommand myCommand =new SqlCommand(strSQL, myConnection);
myCommand.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.FirstName").Value = tbFirstName.Text;
myCommand.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.LastName").Value = tbLastName.Text;
myCommand.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8);
myCommand.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString();

return myCommand.ExecuteScalar().ToString();
}

|||Thanks Mansoorl! I tried that and it worked. In response to your question about the datareader, the reason I went this route is because I have another function wich requires pulling 2 values. So i was in that mindset. I didnt know about the ExecuteScalar though so thanks for educating me.
Do you mind explaining how i might go about returning 3 values via the data reader if:
Select FirstName, LastName, Company from TBClassSurvey Where AttendID=@.AttendID and SchedID=@.SchedID and survey = '0'";

Thanks again,
Stue
|||publicvoidAttendID()
{
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings("ClassDB"));
string strSQL = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'";
SqlCommand myCommand =new SqlCommand(strSQL, myConnection);
myCommand.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.FirstName").Value = tbFirstName.Text;
myCommand.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.LastName").Value = tbLastName.Text;
myCommand.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8);
myCommand.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString();

SqlDataReader myReader = myCommand.ExecuteReader();
myReader.Read();
string FirstName = myReader["FirstName"].ToString();
string LastName = myReader["LastName"].ToString();
string Company = myReader["Company"].ToString();
myReader.Close();
myConnection.Close();
}
The above code assumes you got something back in result of the query. If there is a possiblity for blank records make sure you use if (myReader.Read()) constuct.
Cheers,|||Thanks again mansoorl! I appretiate you educating me.
Take care,
Stue

getting 100 rows with values from 1 - 100

I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
KarlCheck out:
http://msdn.microsoft.com/library/d...r />
p03k1.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<klumsy@.xtra.co.nz> wrote in message
news:1115860445.064283.61420@.f14g2000cwb.googlegroups.com...
I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
Karl

Getting #Error with aggregate function

Why doesn't this return a value?
=Sum(IIf(Fields!GroupCode.Value = 10, Fields!Rating.Value, 0))
I am getting #Error as the value. This however gives me a value:
=Sum(IIf(Fields!GroupCode.Value = 10, 1, 0))
However, I need the above to work...I need it to sum the rating if it's
part of a particular group.Here are some additional findings...
The following code works:
=Sum(IIf(Fields!GroupCode.Value = 10, CInt(Fields!Rating.Value), 0))
The following code fails:
=Sum(IIf(Fields!GroupCode.Value = 10, 1.20, 0))
=Sum(IIf(Fields!GroupCode.Value = 10, CDbl(Fields!Rating.Value), 0))
Why is it that it can only sum up intergers?|||I figured it out!!
The following code works:
=Sum(IIf(Fields!GroupCode.Value = 10, 1.20, 0.0))
Both the true and false values need to be of the same type. By return
0 as my false condition value, and 1.2 as my true condition, it caused
it to fail because it's returning different data types base on the
different condition.
This is bad, MS needs to fix this.

Getting "infinity" when running this expression

When I run this expression, percentage, I'm still getting infinity as my
return when I have a zero in one of the columns
=IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))=0, 0,
SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))) /
IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1,
Fields!APR_CNT.Value,0)) = 0, 1, SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value)
= DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0)))
Any ideas where I'm screwing up... Thanks in advanceThe IIF is a VB function. All function arguments are evaluated immediately
before the function is called. So this will fail: IIF(1 = 1, 1/1, 1/0) even
though it seems like 1/0 should not be evaluated because the condition (1=1)
is true.
This is what most likely happening in your expression.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:C3944D44-31B9-449C-ABC3-DFF6036BC46D@.microsoft.com...
> When I run this expression, percentage, I'm still getting infinity as my
> return when I have a zero in one of the columns
> =IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) =DATEPART("yyyy",NOW()),
> Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) => DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))=0, 0,
> SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
> Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) => DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))) /
> IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) =DATEPART("yyyy",NOW())-1,
> Fields!APR_CNT.Value,0)) = 0, 1,
SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value)
> = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0)))
>
> Any ideas where I'm screwing up... Thanks in advance

getdate() function

im trying to use getdate to just return me the date rather than date time
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
im just after "13/07/2005"
cheers
mark"mark" <mark@.remove.com> wrote in message
news:1121253845.64331.0@.despina.uk.clara.net...
> im trying to use getdate to just return me the date rather than date time
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> im just after "13/07/2005"
> cheers
> mark
>
i fixed it with this crazy procedure, surely theres an easier way
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
declare @.month varchar(10)
select @.month =datepart(mm,@.todaysdate)
declare @.day varchar(10)
select @.day =datepart(dd,@.todaysdate)
declare @.year varchar(10)
select @.year =datepart(yyyy,@.todaysdate)
select @.todaysdate = @.day +'/' + @.month + '/' + @.year
cheers
mark|||Mark,
have a look at this:
select convert(varchar(8),getdate(),3)
Rgds,
Paul Ibison, SQL Server MVP|||Mark,
You really need to understand that SQL Server does not have a DATE or a TIME
datatype. It only has DATETIME or SMALLDATETIME. It either case it always
includes the time portion. Even if you declare a DATETIME and only specify
the date portion it will automatically add the time of midnight. The only
want to display just the date portion (without using a gui that formats it
for you) is to convert it into a string. In your case you are trying to
stuff it back into a smalldatetime datatype which will simply add the time
portion back on again. Change the datatype of the variable to varchar and
you will make life a lot easier.
Andrew J. Kelly SQL MVP
"mark" <mark@.remove.com> wrote in message
news:1121254148.64416.0@.despina.uk.clara.net...
> "mark" <mark@.remove.com> wrote in message
> news:1121253845.64331.0@.despina.uk.clara.net...
> i fixed it with this crazy procedure, surely theres an easier way
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> declare @.month varchar(10)
> select @.month =datepart(mm,@.todaysdate)
> declare @.day varchar(10)
> select @.day =datepart(dd,@.todaysdate)
> declare @.year varchar(10)
> select @.year =datepart(yyyy,@.todaysdate)
> select @.todaysdate = @.day +'/' + @.month + '/' + @.year
> cheers
> mark
>|||i might not have explained it well enough,
im trying to put the currentdate into a column in a database on an insert
using getdate()
currently using getdate() and getting current date and time - which is not
what i need, i only need to record the date not the time|||You should believe us that there IS NO WAY getting only the date from the
getdate() function, SQL Server has no idea about only a date, thats not now
as a datetime type, the only thing would be to insert something using the
convert function like CONVERT(varchar(10), Getdate(),120) or something like
that, instead of using that you can change to IDW 3 on SQL Server 2005 where
actually was a understanding of TIME OR DATE, but they changed it in further
development, but summarized, there is now way for doing that.
HTH, Jens Suessmeyer.
"mark" wrote:

> i might not have explained it well enough,
> im trying to put the currentdate into a column in a database on an insert
> using getdate()
> currently using getdate() and getting current date and time - which is not
> what i need, i only need to record the date not the time
>
>|||"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> You should believe us that there IS NO WAY getting only the date from the
> getdate() function, SQL Server has no idea about only a date, thats not
now
> as a datetime type, the only thing would be to insert something using the
> convert function like CONVERT(varchar(10), Getdate(),120) or something
like
> that, instead of using that you can change to IDW 3 on SQL Server 2005
where
> actually was a understanding of TIME OR DATE, but they changed it in
further
> development, but summarized, there is now way for doing that.
> HTH, Jens Suessmeyer.
>
so you would recommend passing the date from an app to the stored procedure
instead ?
(might be easier)
cheers
mark|||Mark:
Even passing the date to a stored procedure will not work. The database
will STORE your date as a datetime type which means if you pass '13/07/2005'
it will store it as '13/07/2005 00:00:00.000'. You can use an app to only
display and edit the date, but the date will always store as a datetime type
(which will add a MIDNIGHT time). You can also use the convert function to
display your datetime as just a "date string" using CONVERT(VARCHAR(10),
GETDATE(), 103) but as you can see, this actually converts your date into a
string and is treated as a string from then on (sorting is string based
then). Now if you actually want it strip out the time element of GETDATE()
you can use CAST(CONVERT(VARCHAR(10), GETDATE(), 102) AS DATETIME) which
will give you today's date with a midnight time. This will match any where
statement where you just specify just a date e.g. DateField = '2005-07-13'
because this will be converted automatically to '2005-07-13 00:00:00.000'
The question is "Why do you care so much that the database ONLY store the
date?" After all, the database never stores '13/07/2005' in that exact
format anyway. It stores it as a floating value that is calculated from a
set point in time. If you store something in a datetime field, I can get it
out in any format I desire (see the table listing under the "CAST and
CONVERT" topic in BOL). Which is the way it should be to allow for
international usage. In the UK you can display it in UK style and in the US
you can display in the US style. Same date, just displayed differently.
Scott
"mark" <mark@.remove.com> wrote in message
news:1121266783.5639.0@.lotis.uk.clara.net...
> "Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> now
> like
> where
> further
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>

getdate() function

im trying to use getdate to just return me the date rather than date time
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
im just after "13/07/2005"
cheers
mark
"mark" <mark@.remove.com> wrote in message
news:1121253845.64331.0@.despina.uk.clara.net...
> im trying to use getdate to just return me the date rather than date time
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> im just after "13/07/2005"
> cheers
> mark
>
i fixed it with this crazy procedure, surely theres an easier way
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
declare @.month varchar(10)
select @.month =datepart(mm,@.todaysdate)
declare @.day varchar(10)
select @.day =datepart(dd,@.todaysdate)
declare @.year varchar(10)
select @.year =datepart(yyyy,@.todaysdate)
select @.todaysdate = @.day +'/' + @.month + '/' + @.year
cheers
mark
|||Mark,
have a look at this:
select convert(varchar(8),getdate(),3)
Rgds,
Paul Ibison, SQL Server MVP
|||Mark,
You really need to understand that SQL Server does not have a DATE or a TIME
datatype. It only has DATETIME or SMALLDATETIME. It either case it always
includes the time portion. Even if you declare a DATETIME and only specify
the date portion it will automatically add the time of midnight. The only
want to display just the date portion (without using a gui that formats it
for you) is to convert it into a string. In your case you are trying to
stuff it back into a smalldatetime datatype which will simply add the time
portion back on again. Change the datatype of the variable to varchar and
you will make life a lot easier.
Andrew J. Kelly SQL MVP
"mark" <mark@.remove.com> wrote in message
news:1121254148.64416.0@.despina.uk.clara.net...
> "mark" <mark@.remove.com> wrote in message
> news:1121253845.64331.0@.despina.uk.clara.net...
> i fixed it with this crazy procedure, surely theres an easier way
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> declare @.month varchar(10)
> select @.month =datepart(mm,@.todaysdate)
> declare @.day varchar(10)
> select @.day =datepart(dd,@.todaysdate)
> declare @.year varchar(10)
> select @.year =datepart(yyyy,@.todaysdate)
> select @.todaysdate = @.day +'/' + @.month + '/' + @.year
> cheers
> mark
>
|||i might not have explained it well enough,
im trying to put the currentdate into a column in a database on an insert
using getdate()
currently using getdate() and getting current date and time - which is not
what i need, i only need to record the date not the time
|||You should believe us that there IS NO WAY getting only the date from the
getdate() function, SQL Server has no idea about only a date, thats not now
as a datetime type, the only thing would be to insert something using the
convert function like CONVERT(varchar(10), Getdate(),120) or something like
that, instead of using that you can change to IDW 3 on SQL Server 2005 where
actually was a understanding of TIME OR DATE, but they changed it in further
development, but summarized, there is now way for doing that.
HTH, Jens Suessmeyer.
"mark" wrote:

> i might not have explained it well enough,
> im trying to put the currentdate into a column in a database on an insert
> using getdate()
> currently using getdate() and getting current date and time - which is not
> what i need, i only need to record the date not the time
>
>
|||"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> You should believe us that there IS NO WAY getting only the date from the
> getdate() function, SQL Server has no idea about only a date, thats not
now
> as a datetime type, the only thing would be to insert something using the
> convert function like CONVERT(varchar(10), Getdate(),120) or something
like
> that, instead of using that you can change to IDW 3 on SQL Server 2005
where
> actually was a understanding of TIME OR DATE, but they changed it in
further
> development, but summarized, there is now way for doing that.
> HTH, Jens Suessmeyer.
>
so you would recommend passing the date from an app to the stored procedure
instead ?
(might be easier)
cheers
mark
|||Mark:
Even passing the date to a stored procedure will not work. The database
will STORE your date as a datetime type which means if you pass '13/07/2005'
it will store it as '13/07/2005 00:00:00.000'. You can use an app to only
display and edit the date, but the date will always store as a datetime type
(which will add a MIDNIGHT time). You can also use the convert function to
display your datetime as just a "date string" using CONVERT(VARCHAR(10),
GETDATE(), 103) but as you can see, this actually converts your date into a
string and is treated as a string from then on (sorting is string based
then). Now if you actually want it strip out the time element of GETDATE()
you can use CAST(CONVERT(VARCHAR(10), GETDATE(), 102) AS DATETIME) which
will give you today's date with a midnight time. This will match any where
statement where you just specify just a date e.g. DateField = '2005-07-13'
because this will be converted automatically to '2005-07-13 00:00:00.000'
The question is "Why do you care so much that the database ONLY store the
date?" After all, the database never stores '13/07/2005' in that exact
format anyway. It stores it as a floating value that is calculated from a
set point in time. If you store something in a datetime field, I can get it
out in any format I desire (see the table listing under the "CAST and
CONVERT" topic in BOL). Which is the way it should be to allow for
international usage. In the UK you can display it in UK style and in the US
you can display in the US style. Same date, just displayed differently.
Scott
"mark" <mark@.remove.com> wrote in message
news:1121266783.5639.0@.lotis.uk.clara.net...
> "Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> now
> like
> where
> further
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>

getdate() function

im trying to use getdate to just return me the date rather than date time
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
im just after "13/07/2005"
cheers
markMark,
have a look at this:
select convert(varchar(8),getdate(),3)
Rgds,
Paul Ibison, SQL Server MVP|||Mark,
You really need to understand that SQL Server does not have a DATE or a TIME
datatype. It only has DATETIME or SMALLDATETIME. It either case it always
includes the time portion. Even if you declare a DATETIME and only specify
the date portion it will automatically add the time of midnight. The only
want to display just the date portion (without using a gui that formats it
for you) is to convert it into a string. In your case you are trying to
stuff it back into a smalldatetime datatype which will simply add the time
portion back on again. Change the datatype of the variable to varchar and
you will make life a lot easier.
--
Andrew J. Kelly SQL MVP
"mark" <mark@.remove.com> wrote in message
news:1121254148.64416.0@.despina.uk.clara.net...
> "mark" <mark@.remove.com> wrote in message
> news:1121253845.64331.0@.despina.uk.clara.net...
>> im trying to use getdate to just return me the date rather than date time
>> declare @.todaysdate smalldatetime
>> select @.todaysdate= getdate()
>> im just after "13/07/2005"
>> cheers
>> mark
> i fixed it with this crazy procedure, surely theres an easier way
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> declare @.month varchar(10)
> select @.month =datepart(mm,@.todaysdate)
> declare @.day varchar(10)
> select @.day =datepart(dd,@.todaysdate)
> declare @.year varchar(10)
> select @.year =datepart(yyyy,@.todaysdate)
> select @.todaysdate = @.day +'/' + @.month + '/' + @.year
> cheers
> mark
>|||You should believe us that there IS NO WAY getting only the date from the
getdate() function, SQL Server has no idea about only a date, thats not now
as a datetime type, the only thing would be to insert something using the
convert function like CONVERT(varchar(10), Getdate(),120) or something like
that, instead of using that you can change to IDW 3 on SQL Server 2005 where
actually was a understanding of TIME OR DATE, but they changed it in further
development, but summarized, there is now way for doing that.
HTH, Jens Suessmeyer.
"mark" wrote:
> i might not have explained it well enough,
> im trying to put the currentdate into a column in a database on an insert
> using getdate()
> currently using getdate() and getting current date and time - which is not
> what i need, i only need to record the date not the time
>
>|||"Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> You should believe us that there IS NO WAY getting only the date from the
> getdate() function, SQL Server has no idea about only a date, thats not
now
> as a datetime type, the only thing would be to insert something using the
> convert function like CONVERT(varchar(10), Getdate(),120) or something
like
> that, instead of using that you can change to IDW 3 on SQL Server 2005
where
> actually was a understanding of TIME OR DATE, but they changed it in
further
> development, but summarized, there is now way for doing that.
> HTH, Jens Suessmeyer.
>
so you would recommend passing the date from an app to the stored procedure
instead ?
(might be easier)
cheers
mark|||Mark:
Even passing the date to a stored procedure will not work. The database
will STORE your date as a datetime type which means if you pass '13/07/2005'
it will store it as '13/07/2005 00:00:00.000'. You can use an app to only
display and edit the date, but the date will always store as a datetime type
(which will add a MIDNIGHT time). You can also use the convert function to
display your datetime as just a "date string" using CONVERT(VARCHAR(10),
GETDATE(), 103) but as you can see, this actually converts your date into a
string and is treated as a string from then on (sorting is string based
then). Now if you actually want it strip out the time element of GETDATE()
you can use CAST(CONVERT(VARCHAR(10), GETDATE(), 102) AS DATETIME) which
will give you today's date with a midnight time. This will match any where
statement where you just specify just a date e.g. DateField = '2005-07-13'
because this will be converted automatically to '2005-07-13 00:00:00.000'
The question is "Why do you care so much that the database ONLY store the
date?" After all, the database never stores '13/07/2005' in that exact
format anyway. It stores it as a floating value that is calculated from a
set point in time. If you store something in a datetime field, I can get it
out in any format I desire (see the table listing under the "CAST and
CONVERT" topic in BOL). Which is the way it should be to allow for
international usage. In the UK you can display it in UK style and in the US
you can display in the US style. Same date, just displayed differently.
Scott
"mark" <mark@.remove.com> wrote in message
news:1121266783.5639.0@.lotis.uk.clara.net...
> "Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
>> You should believe us that there IS NO WAY getting only the date from the
>> getdate() function, SQL Server has no idea about only a date, thats not
> now
>> as a datetime type, the only thing would be to insert something using the
>> convert function like CONVERT(varchar(10), Getdate(),120) or something
> like
>> that, instead of using that you can change to IDW 3 on SQL Server 2005
> where
>> actually was a understanding of TIME OR DATE, but they changed it in
> further
>> development, but summarized, there is now way for doing that.
>> HTH, Jens Suessmeyer.
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>sql

GetDate() Does not Return Milliseconds ?

Ladies / Gentlemen
If you run the following select statement you will find as I did
that GetDate() does no obtain Milliseconds - Why Not and can I use something
that does ?
Mark Moss
SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
CONVERT(varchar(20), GETDATE(), 109) AS Expr1Hi Mark
The getdate() function ALWAYS returns milliseconds. It's only when you
convert it to character that the milliseconds might be discarded.
In this situation, you have not provided enough space to hold the
milliseconds. Try varchar(25) instead of varchar(20)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"msnews.microsoft.com" <markmoss@.adelphia.net> wrote in message
news:%234V0y1ucGHA.4428@.TK2MSFTNGP03.phx.gbl...
> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use
> something that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>|||Mark,
GETDATE() returns a datetime, which includes a millisec part. If you do
"SELECT DATEPART(ms,GETDATE())" you'll see this. Your problem is that
you're converting to a 20 char string and the millisec bit it getting
truncated. It's a string truncation issue, not a datetime issue. Try
using a varchar(30) or something bigger.
Also, you ought to use CURRENT_TIMESTAMP rather than GETDATE() as
CURRENT_TIMESTAMP is the ANSI equivalent (GETDATE() is Microsoft
proprietary) and in the majority of cases you should leave presentation
of the data up to the presentation layer (ie. the client) rather than
the DB engine.
*mike hodgson*
http://sqlnerd.blogspot.com
msnews.microsoft.com wrote:

>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||The best way to get the format you want is through an user interface. VB
converts the SQL GETDATE() with milliseconds very well.
You can use
SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr2,
CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr1
and your milliseconds will show
Thanks Kllyj64
"msnews.microsoft.com" wrote:

> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use somethi
ng
> that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||lol..or you could just change the size of your VARCHAR()....
--
Thanks Kllyj64
"kllyj64" wrote:
> The best way to get the format you want is through an user interface. VB
> converts the SQL GETDATE() with milliseconds very well.
> You can use
> SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3)
,
> DATEPART(MS,GETDATE())) AS Expr2,
> CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
> DATEPART(MS,GETDATE())) AS Expr1
> and your milliseconds will show
>
> --
> Thanks Kllyj64
>
> "msnews.microsoft.com" wrote:
>|||Just increase VARCHAR to 40. 20 is too short and thus the reason why
msec is left off.
Mark
On Mon, 8 May 2006 16:19:10 -0600, "msnews.microsoft.com"
<markmoss@.adelphia.net> wrote:

>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>

GetDate() conversion

Can anyone show me how to return Getdate() as dd/mm/yyyySelect CONVERT(Varchar(10),getdate(),103)
HTH, Jens Suessmeyer.
"Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
Newsbeitrag news:1423B0C1-97EF-401C-A007-939C6A86007D@.microsoft.com...
> Can anyone show me how to return Getdate() as dd/mm/yyyy|||Peter
Look at CONVERT system function which has a 'style' parameter in the BOL.
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:1423B0C1-97EF-401C-A007-939C6A86007D@.microsoft.com...
> Can anyone show me how to return Getdate() as dd/mm/yyyy|||Go to this website and it will show you how to get the date converted the wa
y
you want it.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5949
JP
"Peter Newman" wrote:

> Can anyone show me how to return Getdate() as dd/mm/yyyysql

getdate() - 5 hrs

How can i return the getdate value minus 5 hrs
So if getdate is 2005/09/07 7am .. Id like to output to be 2005/09/07 2am
ThanksSELECT DATEADD(hh, -5, GETDATE())
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23gQMybEtFHA.1252@.TK2MSFTNGP09.phx.gbl...
How can i return the getdate value minus 5 hrs
So if getdate is 2005/09/07 7am .. Id like to output to be 2005/09/07 2am
Thanks|||SELECT DATEADD(hour, -5, CURRENT_TIMESTAMP)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hassan" <hassanboy@.hotmail.com> wrote in message news:%23gQMybEtFHA.1252@.TK2MSFTNGP09.phx.
gbl...
> How can i return the getdate value minus 5 hrs
> So if getdate is 2005/09/07 7am .. Id like to output to be 2005/09/07 2am
> Thanks
>|||Or
SELECT CONVERT(CHAR(10),GETDATE(),121)+
REVERSE(LEFT(STUFF(REVERSE(
CONVERT(varchar,getdate(),9)),3,4,SPACE(
0)),11))
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23gQMybEtFHA.1252@.TK2MSFTNGP09.phx.gbl...
> How can i return the getdate value minus 5 hrs
> So if getdate is 2005/09/07 7am .. Id like to output to be 2005/09/07 2am
> Thanks
>

Get XML from SQL Server 2000

I have a stored procedure, that returns a customer record from the customers table in the northwind database.
how can i return back an xml string of the row?
I mean, when the aspx page calls that procudure, I want to have somehting like:
<customers>
<customer>
<customerid>xxx</customerid>
<companyname>rrr</companyname>
</customer>
</customers>
can a stored procedure return such a string in xml form?
thanks alot

Read up on the FOR XML clause. Here's an article:http://www.sqljunkies.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk and there's quite a bit of good info in BOL.

Get Value to Return to VB.Net

I have the following stored procedure:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
@.TCoName char(50),
@.TCoAddress char(50),
etc.
@.UserID int OUTPUT,
@.TCoID int OUTPUT
AS
INSERT INTO tblLogin
VALUES
(
@.UserName,
@.Password,
@.EmailAddress
)
Declare @.Ident int
Select @.UserID = @.@.IDENTITY
Select @.Ident = @.UserID
INSERT INTO tblTCompany
VALUES
(
@.Ident,
@.TCoName,
@.TCoAddress,
etc.....
)
Declare @.Ident2 int
Select @.TCoID = @.@.IDENTITY
Select @.Ident2 = @.TCoID
I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I get
the two applications to "talk" to each other?
Any suggestions will be greatly appreciated!
Sandy> I need to grab the @.Ident2 value into VB.Net (for a Web App).
You have a couple of options. One method is to return the value as a result
set:
SELECT @.Ident2
Another technique is to return the value as an OUTPUT parameter:
ALTER Procedure spInsert
@.UserName char(50),
@.Password char(15),
@.EmailAddress char(60),
etc.,
@.Ident2 OUT
AS
...
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>I have the following stored procedure:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> @.TCoName char(50),
> @.TCoAddress char(50),
> etc.
> @.UserID int OUTPUT,
> @.TCoID int OUTPUT
> AS
> INSERT INTO tblLogin
> VALUES
> (
> @.UserName,
> @.Password,
> @.EmailAddress
> )
> Declare @.Ident int
> Select @.UserID = @.@.IDENTITY
> Select @.Ident = @.UserID
> INSERT INTO tblTCompany
> VALUES
> (
> @.Ident,
> @.TCoName,
> @.TCoAddress,
> etc.....
> )
> Declare @.Ident2 int
> Select @.TCoID = @.@.IDENTITY
> Select @.Ident2 = @.TCoID
> I need to grab the @.Ident2 value into VB.Net (for a Web App). How do I
> get
> the two applications to "talk" to each other?
> Any suggestions will be greatly appreciated!
> Sandy|||Hi Dan -
Thanks for your reply. How do you refer to the value in VB.Net, though?
What's the VB code you would write?
Sandy
"Dan Guzman" wrote:

> You have a couple of options. One method is to return the value as a resu
lt
> set:
> SELECT @.Ident2
> Another technique is to return the value as an OUTPUT parameter:
> ALTER Procedure spInsert
> @.UserName char(50),
> @.Password char(15),
> @.EmailAddress char(60),
> etc.,
> @.Ident2 OUT
> AS
> ...
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sandy" <Sandy@.discussions.microsoft.com> wrote in message
> news:F826D886-345C-48DF-BE65-3725BA80E920@.microsoft.com...
>
>|||Sandy,
Here's my table definition
CREATE TABLE dbo.Report (
ReportID int IDENTITY (1, 1) NOT NULL ,
Descr varchar (50) NOT NULL ,
ReportName varchar (50) NOT NULL
)
And the associated SP. Notice the @.ReportID as OUTPUT
and the SET @.ReportID as the last statement. That
returns the identity value in the OUTPUT parameter.
---
CREATE PROCEDURE dbo.usp_Report_Ins
@.Descr varchar(50),
@.ReportName varchar(50),
@.ReportID int OUTPUT
AS
INSERT INTO dbo.Report (
Descr,
ReportName
)
VALUES (
@.Descr,
@.ReportName
)
SET @.ReportID = SCOPE_IDENTITY()
This is a snippet of the Insert code. I use the the
Microsoft Data Access Application Block to do the
SQL stuff (SqlHelper statements) to update the DB.
Use whatever code works there. The last statement
retrieves the value of the identity field.
---
Dim params() As SqlParameter = New SqlParameter(2) {}
params(0) = New SqlParameter("@.Descr", Reports.Descr)
params(1) = New SqlParameter("@.ReportName", Reports.ReportName)
params(2) = New SqlParameter("@.ReportID", Reports.ReportID)
params(2).Direction = ParameterDirection.Output
SqlHelperParameterCache.CacheParameterSet(ConnectionSettings.cnString,
_
"usp_Report_Ins", params)
Dim result As Integer =
SqlHelper.ExecuteNonQuery(ConnectionSettings.cnString, _
CommandType.StoredProcedure, "usp_Report_Ins",
params)
dim PrimaryKey as Integer = CInt(params(2).Value)
On Thu, 10 Feb 2005 07:09:02 -0800, "Sandy"
<Sandy@.discussions.microsoft.com> wrote:
>Hi Dan -
>Thanks for your reply. How do you refer to the value in VB.Net, though?
>What's the VB code you would write?
>Sandy
>
>"Dan Guzman" wrote:
>|||larzeb's example shows how you can get a parameter output value. To
retrieve a value returned as a single-row single-column result, you can use
a number of methods, such as SqlCommand.ExecuteScalar,
SqlCommandExecuteReader or SqlDataAdapter.Fill.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:EBDC9678-1001-4809-A0C5-A524115190B0@.microsoft.com...
> Hi Dan -
> Thanks for your reply. How do you refer to the value in VB.Net, though?
> What's the VB code you would write?
> Sandy
>
> "Dan Guzman" wrote:
>

Get top 3 records for each ...

to relate my question to the pubs DB...

I want to return up to 3 titles for each publisher. The criteria

So if a publisher only has
1 title = return 1
2 titles = return 2
3 titles = return 3
4 titles = return only 3
>4 titles = return only 3

To make it more interesting, lets return the first 3 alphabetically as well...

ThanksTry this:

select * from titles a
where title_id in
(select top 3 title_id
from titles b
where a.pub_id = b.pub_id)
order by pub_id|||/*
JUST A LITTLE ADJUSTED
*/

select
case when t.title_id=(
select min( t3.title_id)
from titles t3
where t3.pub_id = t.pub_id and t3.title=(select min(t4.title) from titles t4 where t4.pub_id = t3.pub_id)
)
then p.pub_name else '' end
,t.title
from titles t
join publishers p on t.pub_id=p.pub_id
where title_id in
(
select top 3 t2.title_id
from titles t2
where t2.pub_id = t.pub_id
order by t2.title
)
order by p.pub_name,t.title

Get Time of Linked Server

Hello,
I have a processes which access a SQL Server 2000 in Brazil, Japan,
China.. and a few other places. I would like to return the localtime
of the linked server.
I cannot find anyway to do this, and can't find a setting in any of
the tables which shows the current timezone.
Any help would be great.
AllanSQL server does not care about Time Zones and does not store it internally.
The Windows Host gives it the time and that all what it wants and needs.
You can get the time on the remote servers by using
SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT CURRENT_TIMESTAMP')
If you need to know it's timezone, you need to access the registry on the
remote machine as Windows stores it here.
Regards
Mike
"Allan Martin" wrote:

> Hello,
> I have a processes which access a SQL Server 2000 in Brazil, Japan,
> China.. and a few other places. I would like to return the localtime
> of the linked server.
> I cannot find anyway to do this, and can't find a setting in any of
> the tables which shows the current timezone.
> Any help would be great.
> Allan
>

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
"'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.
How about this?
SELECT TABLE_SCHEMA, TABLE_NAME=20
FROM INFORMATION_SCHEMA.TABLES=20
WHERE TABLE_TYPE =3D 'BASE TABLE'
--=20
Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =
news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
>=20
> CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type =
=3D
> "'Table'"
>=20
> it won't do it as it can only work in its own context. I have also =
tried
> using the use command with a database name as a parameter to point it =
at the
> database. It won't let me do that either. Any ideas, Regards.
>=20
>
|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:

>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at the
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type = "'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.How about this?
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE =3D 'BASE TABLE'
-- Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
> > CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type ==3D
> "'Table'"
> > it won't do it as it can only work in its own context. I have also =tried
> using the use command with a database name as a parameter to point it =at the
> database. It won't let me do that either. Any ideas, Regards.
> >|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:
>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at the
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
"'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.How about this?
SELECT TABLE_SCHEMA, TABLE_NAME=20
FROM INFORMATION_SCHEMA.TABLES=20
WHERE TABLE_TYPE =3D 'BASE TABLE'
--=20
Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =
news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
>=20
> CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type =
=3D
> "'Table'"
>=20
> it won't do it as it can only work in its own context. I have also =
tried
> using the use command with a database name as a parameter to point it =
at the
> database. It won't let me do that either. Any ideas, Regards.
>=20
>|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:

>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at th
e
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

get the AM/PM value stored in a database

How do I just return if a datetime field is AM or PM from sql server?
thanx
weisenbrI believe you have to use DATEDIFF and get the number of seconds elapsed since the last midnight. Then, if it's greater than 12x60x60 = 43,200, it's PM|||Depending on where you are trying to do it something like this would work:

SELECT CASE WHEN DATEPART(hh,getdate()) > 11 THEN 'PM' ELSE 'AM' END