tsql

How do I execute sql text passed as an sp parameter?

I have a stored procedure with an nvarchar parameter. I expect callers to supply the text for a sql command when using this SP. How do I execute the supplied sql command from within the SP? Is this even possible?- I thought it was possible using EXEC but the following: EXEC @script errors indicating it can't find a stored procedur...

How to filter a table by T_SQL in SQL Server 2005, using a specific time for a column of "DateTime" datatype

hi How to filter a table by T_SQL in SQL Server 2005, using a specific time for a column of "DateTime" datatype? Thank you ...

MySQL JOIN ON with WHERE

I have 2 tables and want to query all the album data joined with the artist artists: artist_id | name albums: album_id | artist_id | songs This gives me only the first row SELECT * FROM albums JOIN artists ON artists.artist_id = albums.artist_id I want to specify an album id SELECT * FROM albums WHERE album_id = 101 JOIN arti...

Looking for a General "Minimum" User Defined Function

I created the following function to simplify a piece of particularly complex code. CREATE FUNCTION [dbo].[DSGetMinimumInt] (@First INT, @Second INT) RETURNS INT AS BEGIN IF @First < @Second RETURN @First RETURN @Second END However, it only works for the INT datatype. I know I could create one for numeric and possibly for Varc...

Testing stored procedures' logic

We have quite a bit of logic in TSQL stored procedures. Being a big fan of automated tests, I started writing automated tests for stored procedures. I test stored procedures by calling them from a C# project. I make all tests touching the database inherit from a base class the ensures the test is run in a TransactionScope that is neve...

Hash Code in SQL Server?

I am trying to solve a unique problem. Thanks for any help with this. Is there an easy way to generate some kind of a hash code for a record based on several of its field values? I thought about just creating a view with an extra column that concatenates all of the values that I want to check, but I'm checking to see if there is a bet...

Doing a join across two databases with different collations on SQL Server and getting an error.

I know, I know with what I wrote in the question I shouldn't be surprised. But my situation is slowly working on an inherited POS system and my predecessor apparently wasn't aware of JOINs so when I looked into one of the internal pages that loads for 60 seconds I see that it's a fairly quick, rewrite these 8 queries as one query with J...

Left Join and Where Clause

Query - Select * FROM tbl1 LEFT OUTER JOIN tbl2 ON tbl1.id = tbl2.id AND tbl2.col2 = 'zyx' AND tbl2.col3 = 'abc' WHERE tbl1.col1 = 'pqr' Here I'm doing left outer join and using 'and' along with join. (First I was using joins 'and' part in 'where' but my results were not correct) My q...

How do I drop a function if it already exists?

I know this must be simple, but how do I preface the creation of a function with a check to see if it already exists? If it exists, I want to drop and re-create it. ...

sql conditional insert if row doesn't already exist

I'm creating a sproc that will insert rows into a 'staging' table with an insert into + subquery like so: INSERT INTO myStagingTable SELECT col1, col2, col3 FROM myRealTable I need to put a conditional in there somehow to determine if the value from col1 for example already exists on myStagingTable, then don't insert it, just skip tha...

Performance issues with Replace Function in T-SQL

Hi, I have a large table that i am working on and for nearly all of the columns i need to use the replace statement to remove single and double quotes. The code looks like this: SET QUOTED_IDENTIFIER ON Update dbo.transactions set transaction_name1 = Replace(transaction_name1,'''','') Update dbo.transactions set transaction_name2 = Re...

Should I break down large SQL queries (MS)

This is in regards to MS SQL Server 2005. I have an SSIS package that validates data between two different data sources. If it finds differences it builds and executes a SQL update script to fix the problem. The SQL Update script runs at the end of the package after all differences are found. I'm wondering if it is necessary or a good ...

How important is the order of columns in indexes?

I've heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) First off, is what I'm saying correct? If so, am i likely to see large differences in performance by rearranging the order...

T-SQL query performance puzzle: Why does using a variable make a difference?

I'm trying to optimize a complex SQL query and getting wildly different results when I make seemingly inconsequential changes. For example, this takes 336 ms to run: Declare @InstanceID int set @InstanceID=1; With myResults as ( Select Row = Row_Number() Over (Order by sv.LastFirst), ContactID From DirectoryC...

T-SQL SELECT DISTINCT & ROW_NUMBER() OVER Ordering Problem

I'm trying to select DISTINCT rows from a view using ROW_NUMBER() OVER for paging. When I switched the ORDER BY field from a SMALLDATETIME to INT I started getting weird results: SELECT RowId, Title, HitCount FROM ( SELECT DISTINCT Title, HitCount, ROW_NUMBER() OVER(ORDER BY HitCount DESC) AS RowId FROM ou_v_Articles T ) AS Temp WHERE ...

How can I grab the character value of a string, given an index ... in some Sql code?

Hi folks, i've got some string in some sql. i need to find out what the character is, for that string, given a index. eg. DECLARE @someString NVARCHAR(MAX) = 'hi folks' DECLARE @index INT = 4 -- assuming the first index is 1, not 0. now .. how do i get the character at 4th index slot, which is an 'f', in that example above. thanks...

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

HERE IS A QUERY select IssueNo, KendraCode, IssueTime, DateOfIssue, P_MotaBags, P_MotaWeight, P_PatlaBags, P_PatlaWeight, P_SarnaBags, P_SarnaWeight, NewBags,OldBags, TransporterName, TruckNumber, DriverName, TruckOwner,SocietyCode , (SELECT PaddyMotaW, PaddyPatlaW, PaddySarnaW, BagsMota, BagsPatla, BagsSarna,PC_ID, sangrahankendraid...

SQL Server - CHARINDEX always returns 0

I have the following query: SELECT CAST([Action] AS NVARCHAR(4000)) AS CastAction, CHARINDEX(CAST([Action] AS NVARCHAR(4000)), N'StatusChange') AS FoundIndex FROM AuditTrail WHERE action LIKE '%StatusChange%' Action is an NTEXT field - this query returns many rows, matching StatusChange in the action text, but the charindex r...

SQL Join to only 1 row - SQL Server 2005

Hi I have an AllocatedStock table holding a case number (knows as a TPND) and a quantity. I need to select a list of product stock but present this with the product number (known as TPNB) rather than the case number. I also have a ProductLookup table which holds all TPNBs and TPNDs. AllocatedStock AllocatedStockID identity TPND int ...

String formatting in T-SQL

I have added a column to a table that will store a formatted string based on concatenating a number of other columns so I can search over it more easily. In order to save loading the whole table into another app and updating the new column then persisting, I want to write an UPDATE SQL query. But I can't figure out how to take an integer...