how to change the data label in SQL?

I have the following SQL statement..

select month(dbo.udfAddUTCBias(start_date_time, 180)) as report_month, direction as direction, count(*) as total_records from traffic with (nolock) where dbo.udfAddUTCBias(start_date_time, 180) >= '2/24/2007' and dbo.udfAddUTCBias(start_date_time, 180) < DateAdd(dd, 1, '2/24/2007') group by month(dbo.udfAddUTCBias(start_date_time, 180)), direction

but the result of the direction field is 1 and 0..

however, I need to show inbound when the result is 0 and outbound when the result is 1 without changing the actual data in the table.

You could use CASE keyword: http://msdn2.microsoft.com/en-us/library/aa258235(SQL.80).aspx

select month(dbo.udfAddUTCBias(start_date_time, 180)) as report_month

, case direction

when 0 then 'inbound'

when 1 then 'outbound'

end

as direction

, count(*) as total_records from traffic with (nolock) where dbo.udfAddUTCBias(start_date_time, 180) >= '2/24/2007' and dbo.udfAddUTCBias(start_date_time, 180) < DateAdd(dd, 1, '2/24/2007') group by month(dbo.udfAddUTCBias(start_date_time, 180)), direction

how to change date format in a select statement

when i use this command in a aspx file

"SELECT DISTINCT Format$([dbo.classgiven.classdate], 'mm/yyyy') AS monthyear,{.......................

'Format$' is not a recognized function name.

so how do i change date from mm/dd/yyyy to mm/yyyy

Check out the CAST and CONVERT functions in SQL BOL. They have a listing of all the possible combinations of formatting you can do for datetime values.|||

Hi~

Try this:

SELECTRIGHT(CONVERT(VARCHAR(10), Column_Name, 103), 7)AS [MM/YYYY]from Table_Name

Hope it helps.

How to Change 200 to 2.00

Hi,
Can anyone help me on this problem please?
When i use sql statement to pull out data, one of the fields will pull out data like 200 or 300 or sumthing like that.
But now i need to pull out the data as 2.00 instead of 200.
How do i do that?
ThanxSelect Cast(200/100 As Decimal(38, 2))|||The simplest way to turn 200 into 2.00 is to invest it in AT&T stock.

Barring that, why don't you just divide by 100.00? Note that you will need to include the decimal point and placeholders in your divisor so that the result will include 2 digits of precision.

How to catch errors

I have an statement
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
ThanksOn Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?

How to capture the entire DML statement in a DML trigger?

Hi,

In Yukon, is it possible to capture the entire DML statement with the parameter values that triggered the DML trigger inside the trigger body?

Basically trying to see the equivalent as the eventdata() in a DDL trigger, that provides the CommandText()

Rgds

No. This is not possible.|||Thanks for the quick reply UJ.
Was just trying to create a detailed Audit trace. (DDL, DML, App Login & Logout)

Can a CLR Trigger extract such data from the SQL Profiler - say using SMO in Yukon etc. - i'm not sure.
Though this would make it a very heavy trigger, but then we can run it async.

Rgds|||SMO cannot be used within CLR trigger right now. It is not supported. Also, using profiler to do these type of actions from trigger code is not ideal. Triggers should usually be very light weight and efficient. The more complex logic that you put inside your trigger the harder it is in terms of performance, development and management. Also, I am not sure how useful it will be to know which statement actually caused the trigger to fire. Note that this might not be very straight-forward too. For example, the update trigger might be fired by UPDATE statement from different SPs or triggers even. In this case, it might be useful to know the SP call that fired the trigger indirectly. What you are asking for is a call / stack trace which is not available in T-SQL now.|||Thanks UJ.
Shall keep you posted on this.

Can we expect SMO in triggers/ Call stack in Yukon?

How to capture the correct identity value

I have a stored procedure which will do 2 insert statements on 2 different tables. In my 2nd insert statement, I need to know how to capture the exact identity primary key value of the newly inserted record from the first insert statement. I am not sure how to get the correct key value of the new record because there may be more than one user inserting at the same time. Therefore, it is tough to capture the key value that belongs to the user doing his transaction at the time. Please help out. Thanks in advance.

blumonde

Have you tried this?

SELECT SCOPE_IDENTITY()

|||

INSERT INTO Table1 ...

INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

|||

Thanks for your response, gentlemen.

I tried 'Select @.getKEY = @.@.IDENTITY' right after the first insert and it seems to work pretty good so far. I hope I am doing it the right way. However, I am not sure my method is consistant when several users inserting at the same time.

I think it is better to use 'scope_identity' and follow Motley's method above. I think it can handle multi-tasking better than my method.

Motley, what is the Select below for? I have to use it after the second insert?

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

blumonde

|||

Motley:

INSERT INTO Table1 ...

INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

Motley, what is the Select below for? I have to use it after the second insert? Thanks.

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

blumonde

|||The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.|||

Motley:

The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.

Thank you.

blumonde

How to call a stored procedure in asp.net

I have created a stored procedure only with an insert statement in sql server 2005.

How can i call this stored procedure through code in ASP.NET page using vb.

i want to pass one parameter that comes from a text box in asp.net page.

my emailid is:g12garg@.yahoo.co.in pls reply.

Thank you

Gaurav

dim conn as new sqlconnection("Your connection string here")

dim cmd as new sqlcommand("Your stored procedure name here",conn)

cmd.CommandType=CommandType.StoredProcedure

cmd.parameters.add("@.Your parameter name here",Your parameter type here).value=your parameter value here

conn.open

cmd.executenonquery

conn.close

|||

Thank you.

Now i want to encrypt this passing value in the stored procedure. i want the encryption to be done in the stored procedure using symmetric key. So do i need to convert this value to varbinary in the stored procedure. or only in the table?

How to build this expression?

Greetings friends,

I have the following T-SQL CASE statement. I've spent the last 10 minutes trying to convert it to an expression in my derived column component but to no avail.

case
when f.etypeid < 10 then '000' + cast(f.etypeid as varchar)
when f.etypeid > 10 and f.etypeid < 100 then '00' + cast(f.etypeid as varchar)
else
'0' + cast(f.etypeid as varchar)
end

Many thanks for your help in advance.

Hi again guys,

Finally I managed to work it out. Silly me!

The solution to the above is as follows :

etypeid < 10 ? "000" + (dt_str,1,1252)etypeid : etypeid > 10 && etypeid < 100 ? "00" + (dt_str,2,1252)etypeid : "0" + (dt_str,3,1252)etypeid

Sorry for the bother SSIS friends

|||Mark your post as an answer, please.

How to build an expression for a textbox?

Can I build an expression on a textbox with a "Sql Select" statement based
on the dataset? There is a parameter user selected on the dropdown and it
generates a dataset from the store procedure. This parameter is used on a
subquery for this dataset in the store procedure and it will return with the
records containing other parameter IDs. However I need to assign a record on
a textbox and require to filter out the record with the same parameterID
user selected in the dropdown. Thanks.
Eg. Parameters!Person.Value=2 selected in the dropdown
Recordset: SearchName
PersonID LName FName
1 Doe John
2 Doe Peter
3 Doe Jane
I need to assign the the name Doe, Peter on a textbox.If I understand correctly, you just want to use the data from the result set
that is returned from the data set.
If you go to the text box where you want to add the expression, right-click
that box, and click on the 'Expression' option.
In the Expression builder you can click the expansion (+) button next to
Fields, then select the Last Name, and click the insert button. Then in the
expression box, type a string that would add a comma, then finish by adding
the first name field to the expression box.
The result in the Expression box would look something like:
=Fields!LName.Value + ", " + Fields!FName.Value
Give that a try and see what happens.
"Paul" wrote:
> Can I build an expression on a textbox with a "Sql Select" statement based
> on the dataset? There is a parameter user selected on the dropdown and it
> generates a dataset from the store procedure. This parameter is used on a
> subquery for this dataset in the store procedure and it will return with the
> records containing other parameter IDs. However I need to assign a record on
> a textbox and require to filter out the record with the same parameterID
> user selected in the dropdown. Thanks.
> Eg. Parameters!Person.Value=2 selected in the dropdown
> Recordset: SearchName
> PersonID LName FName
> 1 Doe John
> 2 Doe Peter
> 3 Doe Jane
> I need to assign the the name Doe, Peter on a textbox.
>
>