SQL Server 2012 :: Behavior Of Brackets In Select Clause When Declaring Variables?
Oct 11, 2014
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar
I have an SSIS package which uses variables and foreach loop containers so connect to multiple instances to retrieve config data. I am adding an extra step to include the port of each instance.
I used a select from a central table to get the connection strings, which is put into [User::Server2]
I have a two tables each having a uniqueidentifier column person_id
I am trying to a select statement where I want a list of the person_id's in one table that are not in another table.
-- insert into wch_needed those who need checked
insert into #wch_needed (person_id, rendered_by ) select distinct e.person_id, e.rendered_by from #wch_who o, encounter e where o.person_id not in (select distinct person_id from #wch_have ) and o.person_id = e.person_id
the where conditional
where o.person_id not in (select distinct person_id from #wch_have )
I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:
EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek
Correlated Subquery: SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek), FROM dbo.TableABC a
I got this to work one time before, but now I am getting the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So I know my code is pretty far off on the variables, but I couldn't find info on how to do this anywhere. Do I need to use sub selects to set the variables? Or something other than variables?
How do I set a variable to represent all of the data. For example using SELECT * will pull all of the data. Is there any symbol or way to declare and set a variable to do the same exact concept. In my query I have set many different variables which are used later on in my where clause but depending on what information I'm pulling from the data I don't wan the variable to have a specific value and instead pull all the data.
I am writing a stored procedure and have a query where I create a variable from other table
Declare @Sem varchar (12) Null @Decision varchar(1) Null Select emplid,name, Semester Decision1=(select * from tbldecision where reader=1) Decision2=(select * from tbldecision where reader=2) Where Semester=@Sem And Decision1=@Decision
But I am getting error for Decision1 , Decision2. How can I do that.
I have a script component in a data flow that is exhibiting some strange behavior. In the PreExecute event of the data flow, I stuff a recordset into a variable that is declared at the data flow scope. Within the data flow, I use a script component to read in the data from the recordset.
Example:
Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt1 As New System.Data.DataTable
Dim row As System.Data.DataRow
olead.Fill(dt1, Me.Variables.rsIntRateStrata)
If I display the count of the records in the data table dt1, it shows 42 rows, which is correct. Run the package, everything runs as expected. So far, so good.
Now, I set up another source/destination within the same data flow, as well as a script component between them, same as the first flow described above. Now my data flow has two parallel flows (different source & destinations). I copy the same script logic from the first flow into the second. Run the package- no errors, everything is fine... except when I inspect the data, it looks like the transformation isn't working correctly in the second script.
So I display a messagebox of each script component during run time. The first component displays 42 records, while the second displays 0 records? Same variable. Same data flow.
So I delete the first (original) flow from my data flow. Run the package again. Now the messagebox says 42.
What is happening here? Do I have to create two variables to duplicate the same recordset if I need to use it multiple times within the same data flow? Is this a bug?
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as begin Declare @ret int; Declare @EnterVolumeTime int; Declare @ExitVolumeTime int; Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
I'm rewriting a huge FOR XML EXPLICIT procedure to use FOR XML PATH, and need to compare previous output to the refactored one, so i didn't mess up XML structure.
The thing is, i'm not sure that SQL Server will always generate exactly same xml **string**, so i'd rather not compare by:
WHERE CAST(@xml_old AS NVARCHAR(MAX)) = CAST(@xml_new AS NVARCHAR(MAX))
nor do i want to manually validate every node, since the generated xml-structure is quite complex.
They are sent to the sproc as null. Since they could be null I need to exclude them from posting to a temp table
Example
DECLARE @QuantityID uniqueidentifier, @LengthID uniqueidentifier, @CostID uniqueidentifier SET @CostID = NEWID() SELECT @QuantityID as ID UNION ALL SELECT @LengthID UNION ALL SELECT @CostID
Two values are null. I want those excluded from this table
Here is the example of what I am trying to do:
DECLARE @QuantityID uniqueidentifier, @LengthID uniqueidentifier, @CostID uniqueidentifier DECLARE @Temp as Table (id uniqueidentifier NOT NULL Primary key) SET @CostID = NEWID() INSERT INTO @Temp SELECT @QuantityID as ID UNION ALL SELECT @LengthID UNION ALL SELECT @CostID
SQL logic, and I've been working (and researching) this all day with zero success..My goal is to try an pass a variable from an ASP page to a stored procedure, which is utilizing the variable as criteria for a column_name in the where clause.So for example (a simplified version of my query):
@strDept nvarchar(10), @strUser nvarchar(30) -- the asp page will pass f19 to @strDept -- the asp page will pass the logged in username to @strUser
select x, y, z from table1 where @strDept in (@strUser)
The stored procedure does execute, but it returns no values. Is that because the where variable has no data at compile time? If not, I can't think of any reason why this is not working. (I have tried a case statement, before the select to set the variable too and that returned the same result.)
What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint DECLARE @Instance varchar(100) set @Instance = 'The Instance' IF (EXISTS (SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a)) SET @Found = 1 ELSE SET @Found = 0
I have a question regarding the locking behavior of indexed views. We have a 3rd party application and something like the following table:
CREATE TABLE [Person].[Person]( [BusinessEntityID] int NOT NULL, [FirstName] varchar(20) NOT NULL, [MiddleName] varchar(20) NULL,
[code]....
Also the 3rd party application uses an indexed view, which is based on the following query and has the same structure as the base table:
SELECT [BusinessEntityID], [FirstName], [MiddleName], [LastName], SUM([Quantity]) AS [SUMQuantity] FROM [Person].[Person] GROUP BY [BusinessEntityID], [FirstName], [MiddleName], [LastName]
The result is, that the indexed view has nearly the same data / entries as the base table. The indexed view is often queried like this:
SELECT SUM(SUMQuantity) FROM [vPerson] WITH(UPDLOCK, NOEXPAND) WHERE [BusinessEntityID] = 45 AND [FirstName] = 'test'
The base table is also queried very often with update locks (UPDLOCK). Because the indexed view is nearly just a copy of the base table and there is no performance gain (read), I would like to drop the indexed view. I'll then experience more locks / blocks, because now the queries are seperated on two objects.
I have a problem with executing following T-SQL select query.My select statement looks like thisSELECT * FROM (@TableName_FirstPart + @TableName_secondPart)**@TableName_FirstPart & @TableName_SecondPart are Local variables**Im getting Incorrect syntax error for this statement.Following is the error message:"Server: Msg 170, Level 15, State 1, Line 55Line 55: Incorrect syntax near '+'."Is it possible to construct table names in the FROM clause dynamically ?Thanks in advance
From MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:
where [Field1] like @p1 and [Field1] < @p2. Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause. @p1 is always a "Starts-With"-value (something like N'abc%').
The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.
Depending on the table size I see 1M or more reads for these querries.
If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.
I was able to reproduce the issue with a temp table. See code below.
Is there a way to make SQL Server use another strategy when using the parameterized query?
SQL Server Version is 11.0.3401. if object_id('tempdb..#tbl') is not null drop table #tbl; create table #tbl ( [No] nvarchar(20) ,[Description1] nvarchar(250)
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns FROM table WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1') AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
I would like to be able to combine the functionality of IN and LIKE in a WHERE clause. Although the simple AdventureWorks2012 example below illustrates the concept with 3 search criteria, the real-world example I need to apply the concept to has a couple dozen. This returns 50 rows, but requires multiple OR ... LIKE functions:
SELECT DISTINCT c.Name FROM Sales.Store c WHERE c.Name LIKE '% sports %' OR c.Name LIKE '% exercise %' OR c.Name LIKE '%toy%'
What I would like to do is something like this, which doesn't work:
SELECT DISTINCT c.Name FROM Sales.Store c WHERE c.Name IN(LIKE '% sports %', LIKE '% exercise %', LIKE '%toy%')
I could load up a cursor and loop through it, but the syntax is more cumbersome than the multiple LIKE statements, not to mention most SQL programmers are horrified at the mention of the abominable word 'cursor' for performance reasons.
A while back, a "quirky update" method was proposed for lightning fast running totals based on the three-part MSSQL UPDATE's SET statement and tally tables. However, some claimed this was not 100% absolutely guaranteed behavior.
How does the new OVER clause compare in terms of performance ?
DECLARE @Tbl TABLE ( pk int not null primary key identity, N int ) INSERT INTO @Tbl (N) SELECT TOP 1000 1 FROM syscolumns a CROSS JOIN syscolumns b SELECT pk, SUM(pk) OVER (ORDER BY pk ) FROM @Tbl
I am creating a dynamic query where i am appending a where clause something like -
IF (@CurCaptureDate IS NOT NULL) SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + @CurCaptureDate
PS - CD.CaptureDate is datetime and @CurCaptureDate is also datetime
but when executing , it gives this error - Msg 241, Level 16, State 1, Line 169 Conversion failed when converting date and/or time from character string.
i am not able to use convert here with these quotes.
i tried this - SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + CONVERT(VARCHAR(25),@CurCaptureDate )
but it makes it to -
AND CD.CaptureDate = Jul 19 2014 12:00AM. I would need the date in quotes.
Because of the way in which a specific piece of code is written, I'm bound into using a WHERE clause for a report generation.Each Inspection generates a unique Inspection Number. Any re-inspection created from that inspection is assigned that Inspection Number and appended with ".A", ".B", ".C" and so on.
The problem is this: Each row's Primary Key is the "InspectionId" in "dbo.v_InspectionDetailsReports". I need to return not only the data related to that particular InspectionId, but also the data related to any previous related inspection. For example, if I have a main number of CCS-2012 and three re-inspections, CCS-2012.A, CCS-2012.B and CCS-2012.C, and I report on CCS-2012.B, I need all the data for CCS-2012, CCS-2012.A and CCS-2012.B but NOT CCS-2012.C.
I would prefer to not have to do everything in a WHERE statement, but my hands are a bit tied.
The "SELECT * FROM dbo.v_InspectionDetailsReports WHERE . . ." is already hardcoded (don't ask). SELECT * FROM dbo.v_InspectionDetailsReports WHERE ( RefOnly = 0 OR RefOnly IS NULL
I am using an aggregate with the OVER clause.Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.
if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money]) insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints]) selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,
