Showing posts with label return. Show all posts
Showing posts with label return. Show all posts

Friday, March 30, 2012

Question when subtracting two queries

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.Problem resolved. Thinking was off.

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.

|||

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.


TRY below query and check if that works...

select * from
(
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c on t.object_id = c.object_id
) x
left outer join
(
--213
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t on s.major_id = t.object_id
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
) y on x.[DataBase Name] = y.[DataBase Name]
where y.[DataBase Name] is null

Wednesday, March 28, 2012

Question Regarding Stored Procedure??OUTPUTS

I have a stored procedure that I just need to return the output to my program.It is a Select All type statement.I will post my vb code that works when I use both inputs and outputs but not for all output procedure...I dont get it.
Here is the Stored Procedure....

CREATE procedure dbo.IDXAppt_Settings_NET
(
@.SQLADD nvarchar(15)Output,
@.SQLDatabase nvarchar(20)Output,
@.SQLLogin nvarchar(20)Output,
@.SQLPass nvarchar(20)Output
)
as
select
@.SQLADD=SQLAddress,
@.SQLDatabase=SQLDatabase,
@.SQLLogin=SQLLogin,
@.SQLPass=SQLPassword

from
Clinic_Settings

GO

Here is the Vb.Net Code......
To retrieve the elements that does not give me an error just gives me no data...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim consql As New SqlConnection("server=myserver,database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand

Dim parmSQLAddress As SqlParameter
Dim parmDatabase As SqlParameter
Dim parmLogin As SqlParameter
Dim parmSqlPass As SqlParameter

Dim strtest As String
Dim db As String
Dim login As String
Dim pass As String

cmdsql = New SqlCommand("Appt_Settings_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure
parmDatabase = cmdsql.Parameters.Add("@.SQLData", SqlDbType.NVarChar)
parmDatabase.Size = 20
parmDatabase.Direction = ParameterDirection.Output
db = cmdsql.Parameters("@.SQLData").Value

parmLogin = cmdsql.Parameters.Add("@.SQLLogin", SqlDbType.NVarChar)
parmLogin.Size = 20
parmLogin.Direction = ParameterDirection.Output
login = cmdsql.Parameters("@.SQLLogin").Value

parmSqlPass = cmdsql.Parameters.Add("@.SQLPass", SqlDbType.NVarChar)
parmSqlPass.Size = 20
parmSqlPass.Direction = ParameterDirection.Output
pass = cmdsql.Parameters("@.SQLPass").Value

parmSQLAddress = cmdsql.Parameters.Add("@.SQLADD", SqlDbType.NVarChar)
parmSQLAddress.Size = 15
parmSQLAddress.Direction = ParameterDirection.Output
strtest = cmdsql.Parameters("@.SQLADD").Value
consql.Open()
cmdsql.ExecuteNonQuery()

Label1.Text = strtest
End SubYou may get the output value after the stored procedure executed.|||Doesn't look like you want to use OUTPUTs here. You're query will get every row back from the table, so only the last (or is it the first - last I think) row will go into your output params.

Question regarding running a stored procedure as a job step

I am running a stored procedure as a job step and in the stored procedure I use return to pass one of several possible values when there is an error in processing (not an system error) so that the job step will fail. However, even when I return a non-zero value using return the job step completes as successful. What should I be doing so that the job step picks up the non-zero value and then indicates the step failed?

Try using a RAISERROR with a severity greater than 10.

-Sue

|||Thank you very much Sue

Monday, March 26, 2012

question regarding CASE and GROUP BY

I have the below query and I am not sure if this will return an accurate aggregate, I know I cannot just group by my alias GLG_DELEGATE_ID, is this the way to handle aggregates when you have a CASE in the SELECT statement?

SELECT CASE
WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID
END AS GLG_DELEGATE_ID
,COUNT(P.CONSULTATION_ID) ACTIVITY_AMOUNT
FROM
dbo.CONSULTATION C
GROUP BY
C.GLG_DELEGATE_ID
, C.GLG_IDNo, just repeat your CASE code in the WHERE clause:

SELECT CASE WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID
END AS GLG_DELEGATE_ID
,COUNT(P.CONSULTATION_ID) ACTIVITY_AMOUNT
FROM dbo.CONSULTATION C
GROUP BY CASE WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID END
,C.GLG_ID

Saturday, February 25, 2012

question on date (or string after using convert function) comparis

select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
the result return is 'T' why? (2005 should be < 2006)
On Mon, 13 Feb 2006 19:05:26 -0800, kei wrote:

>select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
>the result return is 'T' why? (2005 should be < 2006)
Hi kei,
You are comparing two string constants. They may look like dates to you
and me (though probably not the same dates - I'm from the part of the
world that uses dd/mm/yyyy), but SQL Server doesn't try to interpret
what you write - it takes you literally.
You could try
SELECT CASE WHEN CAST('02/12/2005' AS datetime) >=
CAST('01/02/2006' AS datetime) THEN 'T' ELSE 'F' END
and pray that SQL Server interprets the ambiguous date format the same
way you do.
Or you could switch to a non-ambiguous date format:
SELECT CASE WHEN CAST('20051202' AS datetime) >=
CAST('20060201' AS datetime) THEN 'T' ELSE 'F' END
For more information, check Tibor Karaszi's article on SQL Server date
and time handling: http://www.karaszi.com/SQLServer/info_datetime.asp
Hugo Kornelis, SQL Server MVP

Monday, February 20, 2012

Question on @sql return value

hi,
I have a t-sql script as follows,
Declare @.sql varchar(1000)
set @.sql = select count(*) from table1
exec (@.sql)
I want to know if recordcount = 0
then go line1
else go line2
How do I assign the return value(recordcount)
ThanksDECLARE @.rc INT;
Declare @.sql varchar(1000)
set @.sql = 'select count(*) from table1';
exec (@.sql);
SET @.rc = @.@.ROWCOUNT;
PRINT @.rc
IF @.rc = 0
BEGIN
-- do this
END
ELSE
BEGIN
-- do that
END
--
Aaron Bertrand
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
> hi,
> I have a t-sql script as follows,
> Declare @.sql varchar(1000)
> set @.sql = select count(*) from table1
> exec (@.sql)
> I want to know if recordcount = 0
> then go line1
> else go line2
> How do I assign the return value(recordcount)
> Thanks
>|||That's what I want...
Thanks a lot
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23qP6vQ1HHA.4712@.TK2MSFTNGP04.phx.gbl...
> DECLARE @.rc INT;
> Declare @.sql varchar(1000)
> set @.sql = 'select count(*) from table1';
> exec (@.sql);
> SET @.rc = @.@.ROWCOUNT;
> PRINT @.rc
> IF @.rc = 0
> BEGIN
> -- do this
> END
> ELSE
> BEGIN
> -- do that
> END
> --
> Aaron Bertrand
> SQL Server MVP
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> hi,
>> I have a t-sql script as follows,
>> Declare @.sql varchar(1000)
>> set @.sql = select count(*) from table1
>> exec (@.sql)
>> I want to know if recordcount = 0
>> then go line1
>> else go line2
>> How do I assign the return value(recordcount)
>> Thanks
>>
>

Question on @sql return value

hi,
I have a t-sql script as follows,
Declare @.sql varchar(1000)
set @.sql = select count(*) from table1
exec (@.sql)
I want to know if recordcount = 0
then go line1
else go line2
How do I assign the return value(recordcount)
ThanksDECLARE @.rc INT;
Declare @.sql varchar(1000)
set @.sql = 'select count(*) from table1';
exec (@.sql);
SET @.rc = @.@.ROWCOUNT;
PRINT @.rc
IF @.rc = 0
BEGIN
-- do this
END
ELSE
BEGIN
-- do that
END
Aaron Bertrand
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
> hi,
> I have a t-sql script as follows,
> Declare @.sql varchar(1000)
> set @.sql = select count(*) from table1
> exec (@.sql)
> I want to know if recordcount = 0
> then go line1
> else go line2
> How do I assign the return value(recordcount)
> Thanks
>|||That's what I want...
Thanks a lot
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:e%23qP6vQ1HHA.4712@.TK2MSFTNGP04.phx.gbl...
> DECLARE @.rc INT;
> Declare @.sql varchar(1000)
> set @.sql = 'select count(*) from table1';
> exec (@.sql);
> SET @.rc = @.@.ROWCOUNT;
> PRINT @.rc
> IF @.rc = 0
> BEGIN
> -- do this
> END
> ELSE
> BEGIN
> -- do that
> END
> --
> Aaron Bertrand
> SQL Server MVP
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
>