Tag: variables

Setting variable SQL Procedure

I am working on a database that contains customers, products, timesheets, etc for a store. The question I am working on involves creating a procedure that will change an “on/off” column to off (the product is available (1) by default, and this procedure turns it to 0) I have writen the procedure fine: create proc […]

Set dynamic variable day in SSIS Package

I hope you can help me on this! I really appreciate the help from here! What I’m trying to do is create SSIS package to import a .TXT delimited file into a database. The procedure for doing this with a static file is clear in my mind, but the thing here is that the import […]

In SQL Server query slows dramatically when variable in SELECT clause

I have the following simple code which I run in SQL Server Management Studio: DECLARE @CurDeviceIndex int SET @CurDeviceIndex = 314 SELECT TOP 1 DeviceIndex, DatetimeOccurred FROM [dvm_data].[dbo].[CalculatedData] WHERE DeviceIndex = @CurDeviceIndex ORDER BY ID DESC In some cases this query takes forever to run, actually I never waited until the end. When I instead […]

How to make a temporary variable act as * in a select statement, in t-sql

When writing a select statement with ‘*’ (for example select * (…)), I’d like to use the keyboard combination [shift+2] +[2], instead of [shift+8]. This is because I can find the right shift and 2 button easier on my keyboard than the left shift and 8. Though it may look arbitrary to you, for me […]

How to initialize a variable in stored procedure with select statement

I wonder how I can directly give this variable (@p_txtValue) in the stored procedure (EXEC dbo.bisting_sp_common_Event_SetParameter) a value with a select statement without using SET statement above. This is my code where I initialize @l_txtTemp with a SET statement then the give @p_txtValue the value from it. SELECT ssh.No_ ,ssh.[Bill-to Customer No_] ,RANK () over […]

Convert MSsQL to MySql stored procedure using @variable

I used this SP with MsSQL. How do I convert it to MySql? It would not allow me to create procedure because of syntax errors. The goal is to receive a string from a user like this: (ReportId,UserName) – if found in the variable, send back Allowed else send back Access denied. Thanks. Procedure [dbo].[Permission] […]

Sudden “Must declare the scalar variable” when running example snippets

I was exploring new Transact SQL JSON capabilities by running various example snippets in my regular SQL Server 2014 Management Studio against clean install of SQL Server 2016 Express. I’ve started to understand how it works, but suddenly got bitten by Must declare the scalar variable For example, this loading snippet worked before: SELECT @json […]

How to set the result of a stored procedure to a variable

I am calling a stored procedure like this: EXEC usp123 @F1 = 1, @F2 = ‘A’, @F3 = 5 DECLARE @Result int SET @Result = (EXEC usp123 @F1 = 1, @F2 = ‘A’, @F3 = 5) How do I assign a variable to the output of this stored procedure as the above syntax does not […]

SQL Server XML field – using TSQL variable as XML node sequence indicator

Why does this work? SELECT XDocument .value(‘(/Book/Chapter[@verse=”allo”])[2]’, nvarchar(max)’) FROM XBiblos WHERE Version = 666 But WHY does this not work? Where the only diff is that [2] is replaced by [sql:variable(“@i”)] Which results in error msg ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *’ DECLARE @i int; SET @i = […]

SSRS group variables do not shown in expression editor window

I just found that in SSRS the group variables do not shown in the “variables” category of expression editor Windows. So that means I can’t double click the variable name to use them and I have to type them manually. I confirmed this issue on SSRS 2016 and 2008. Is this a bug or is […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.