Converting delimited varchar @parameter for use in NOT IN()

I am creating a stored procedure which is passed a comma delimited string of
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:

> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

|||I have the same question as KoryS. Anyone have an answer?

Thanks.

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

Convert returned value to Integer

I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.

----------------
DECLARE @.TransactionID int
<-- INSERT statement (after inserting the record, select the identity ID) --->
Select @.TransactionID = @.@.Identity
RETURN
----------------

.
.
.
Dim transactionID As Integer
connection.Open()
Dim reader As SqlDataReader
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim p3 As New SqlParameter("@.TransactionID", SqlDbType.Int)
p3.Direction = ParameterDirection.Output
transactionID = Convert.ToInt16(p3.Value)
connection.Close()
.
.
.

I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.

I tried many different ways of conversion but couldn't resolve the error. Could anyone help?

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

|||

ndinakar:

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

hmm.. pardon me as this is my first time with asp.net

In the article:

myCommand.Parameters.Add(New SqlParameter("@.ProductId",SqlDbType.Int))

myCommand.Parameters.Direction = ParameterDirection.Output

The line in red doesn't work. I guess I'm using asp.net 2.0, that's why... so I changed to something like

command.Parameters("@.ProductID").Direction = ParameterDirection.Output

Yupz.. I'm retrieving through output parameters, I tried ExecuteReader but still couldn't work.

Actually I have these two tables for a shopping cart project - OrderTransaction and OrderDetails

OrderTransaction stores general information of TransactionID, Status, ShippingAddress etc of an order. OrderDetails stores details of a particular transaction which has foreign keys references to the Ids of OrderTransaction, Customers and Products table.
Hence, the reason for retrieving the TransactionID is so that I can create records for the items in the shopping cart in the OrderDetails for the particular transaction.

Code:

Dim transactionID As Integer
Dim connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
Dim command As SqlCommand = New SqlCommand("AddOrders", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@.CustomerID", Context.Session("myID"))
command.Parameters.AddWithValue("@.DateCreated", DateTime.Now.ToString())
command.Parameters.AddWithValue("@.Verified", 0)
command.Parameters.AddWithValue("@.Cancelled", 0)
command.Parameters.AddWithValue("@.CustomerName", nameTB.Text)
command.Parameters.AddWithValue("@.ShippingAddress", shippingaddressTB.Text)
command.Parameters.AddWithValue("@.CustomerEmail", emailTB.Text)
command.Parameters.Add("@.TransactionID", SqlDbType.SmallInt)
command.Parameters("@.TransactionID").Direction = ParameterDirection.Output
connection.Open()
command.ExecuteNonQuery()
transactionID = Convert.ToInt16(command.Parameters("@.TransactionID").Value)
connection.Close()

Stored Procedure:

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50)
)

AS

DECLARE @.TransactionID int

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated), @.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)

Select @.TransactionID = @.@.Identity

RETURN

The error is
"Procedure or function AddOrders has too many arguments specified."
Line 161: command.ExecuteNonQuery()

I think that's because I did something wrong in the lines ingreen.Appreciate if anyone helps. Thanks in advanced.
|||(1) The error is exactly what it says. You are trying to add @.TransactionId as a parameter when you havent declared it as a parameter in your stored proc. modify your stored proc as follows:
(2) SCOPE_IDENTITY() is more efficient than @.@.IDENTITY. Check out books on line for more explanation.
(3) Since the value you are returning is through OUTPUT parameters, use ExecuteNonQuery.

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50),
@.TransactionId INT OUTPUT
)

AS

BEGIN
SET NOCOUNT ON

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated),@.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)
SELECT @.TransactionID= SCOPE_IDENTITY()

SET NOCOUNT OFF
END

Convert Function Query Error

I am in the middle of creating an editable DatGrid:
Sub AccessoryGrid_EditCommand(source As Object, e As MxDataGridCommandEventArgs)
AccessoryGrid.EditItemIndex = e.Item.ItemIndex
End Sub
Sub AccessoryGrid_BeforeUpdate(source As Object, e As MxDataGridUpdateEventArgs)
e.NewValues.Add("@.AccessoryID",AccessoryGrid.DataSource.DataSource.Tables(0).Rows(e.Item.DataSetIndex)("AccessoryID"))
e.NewValues.Add("@.AccessoryName", CType(e.Item.Cells(1).Controls(0),TextBox).Text)
e.NewValues.Add("@.AccessoryPrice", CType(e.Item.Cells(2).Controls(0),TextBox).Text)
e.NewValues.Add("@.AccessorySold", CType(e.Item.Cells(3).Controls(0),TextBox).Text)
e.NewValues.Add("@.AccessoryDesc", CType(e.Item.Cells(4).Controls(0),TextBox).Text)
e.NewValues.Add("@.AccessoryImage", CType(e.Item.Cells(5).Controls(0),TextBox).Text)
End Sub
For some reason, I get an error message like this:

Server Error in '/' Application.

Disallowedimplicit conversion from data type nvarchar to data type smallmoney,table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERTfunction to run this query.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException:Disallowed implicit conversion from data type nvarchar to data typesmallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Usethe CONVERT function to run this query.
Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.]
 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.PerformSqlCommand(SqlCommand command) +82
 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.Update(String listName, IDictionary selectionFilters, IDictionary newValues) +114
 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnUpdateCommand(MxDataGridUpdateEventArgs e) +869
 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnBubbleEvent(Object source, EventArgs e) +546
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
 Microsoft.Saturn.Framework.Web.UI.MxDataGridItem.OnBubbleEvent(Object source, EventArgs e) +86
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +95
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain() +1277

My main question is, how can I convert my column 'AccessoryPrice' to smallmoney?
I have been trying to get rid of this error by trying to change thefield type within my database with no success, I keep on getting thesame error either way.
I would be very greatful if anybody can help me.I'm also having this error. Any ideas?
|||Well, we'd need to see the query to give you the exact code toexplicitly convert the data. But check out the topic in BooksOnline:CAST and Convert. The code would like something like one of these:

SELECT CAST(myNVarcharColumn AS SmallMoney) AS myNVarcharColumn

or

SELECT CONVERT(smallmoney, myNVarcharColumn) AS myNVarcharColumn