tsql

Validating string lengths based on related database columns

At the one end of my web application I have a database table storing a load of pieces of text. In the middle I have an API that separates my application tiers. At the other end I have a user interface consisting of many TextBoxes (or input type=text form elements, if you prefer). I need the maxlength properties of the TextBoxes to be ...

Select only half the records

I am trying to figure out how to select half the records where an ID is null. I want half because I am going to use that result set to update another ID field. Then I am going to update the rest with another value for that ID field. So essentially I want to update half the records someFieldID with one number and the rest with another nu...

My IN clause leads to a full scan of an index in T-SQL. What can I do?

I have a sql query with 50 parameters, such as this one. DECLARE @p0 int, @p1 int, @p2 int, (text omitted), @p49 int SELECT @p0=111227, @p1=146599, @p2=98917, (text omitted), @p49=125319 -- SELECT [t0].[CustomerID], [t0].[Amount], [t0].[OrderID], [t0].[InvoiceNumber] FROM [dbo].[Orders] AS [t0] WHERE ([t0].[CustomerID]) IN (...

MS SQL 2005 "For XML Path" Node Layout Question

I have the following query Select field1 as 'node1/field1', field2 as 'node1/field2', (Select field3 as 'child1/field3', field4 as 'child1/field4' From table2 FOR XML PATH(''),TYPE,Elements) From Table1 FOR XML PATH('Root'),Elements This produces: <Root> <node1> <field1>data1</field1> <field2>data2<...

SqlDataSource SelectCommand using LIKE does not work

I have the following T-SQL in a SelectCommand: SELECT h.Business, hrl.frn FROM registration hrl INNER JOIN holder h on h.call = hrl.call WHERE (h.Business like '%' + @business + '%' and h.Business is not null) and (hrl.frn = @frn and hrl.frn is not null) business and frn are tied to control parameters and it should return data even...

How Do You Call an MSSQL System Function From ADO/C++?

...specifically, the fn_listextendedproperty system function in MSSQL 2005. I have added an Extended Property to my database object, named 'schemaVersion'. In my MSVC application, using ADO, I need to determine if that Extended Property exists and, if it does, return the string value out of it. Here is the T-SQL code that does what I ...

Importing XML into SQL Server

I can find lots of example on how to import certain types of XML data into SQL Server 2005. But I've been given data in the following format (repeating "row" and "cell" with ID's instead of the tags been named etc: <?xml version="1.0"?> <rows> <row id='1'> <cell id='category'>Simple</cell> <cell id='query'>summary</...

TSQL Writing into a Temporary Table from Dynamic SQL

Consider the following code: SET @SQL1 = 'SELECT * INTO #temp WHERE ...' exec(@SQL1) SELECT * from #temp (this line throws an error that #temp doesn't exist) Apparently this is because the exec command spins off a separate session and #temp is local to that session. I can use a global temporary table ##temp, but then I have to come ...

TSQL Define Temp Table (or table variable) Without Defining Schema?

Is there a way to define a temp table without defining it's schema up front? ...

Evaluate in T-SQL

I've got a stored procedure that allows an IN parameter specify what database to use. I then use a pre-decided table in that database for a query. The problem I'm having is concatenating the table name to that database name within my queries. If T-SQL had an evaluate function I could do something like eval(@dbname + 'MyTable') Current...

How do I Execute SQL Stored Procedures from within another Stored Procedure?

Everytime I refresh my DB with a backup file. I have to run about 10 stored procs separately because the backup file does not contain them. Is there a way to have one single sql script that refrences all these 10 stored procs and just run that ONE file compared to TEN? ...

Using Logic in TSQL

I want to do something like this in TSQL (SQL Server 2005): IF (Column1 = x) { --CTE statement } ELSE { --SQL statement } Any help is appreciated. ...

Porting from MySql to T-Sql. Any INET_ATON() equivalent?

Need to move some code from MySql to TSql. I have a couple of calls to INET_ATON which converts a string which resembles an IPAddress into a number. Is there a T-SQL equivalent? ...

Change the WHERE for a select on only one row depending on the field content

Ok, this may sound a little weird, but what I need to do is simple. I have a list of companies and each one has a description. The site is in 4 languages. My problem comes when I have listed a company in English but the person who is in charge of translating it in French still didn't make the translation, so I have a simple: SELECT TOP(...

Conditionally branching in SQL based on the type of a variable.

Hi everyone. I'm selecting a value out of a table that can either be an integer or a nvarchar. It's stored as nvarchar. I want to conditionally call a function that will convert this value if it is an integer (that is, if it can be converted into an integer), otherwise I want to select the nvarchar with no conversion. This is hitti...

Split Function equivalent in tsql?

I'm looking to split '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...' (comma delimited) into a Table or Table Variable. Does anyone have a Function that returns each one in a row? I am using SQL Server 2008. Thanks. ...

SQL: How to limit the number of records the MERGE statement will insert

Some sample data: DECLARE @TARGET TABLE ( ID INT, value INT ) ; DECLARE @SOURCE TABLE ( ID INT, value INT ) INSERT INTO @TARGET VALUES ( 1, 213 ) INSERT INTO @TARGET VALUES ( 2, 3 ) INSERT INTO @TARGET VALUES ( 3, 310 ) INSERT INTO @TARGET VALUES ( 4, 43 ) INSERT INTO @SOURCE...

SQL: Update a row and returning a column value with 1 query

I need to update a row in a table, and get a column value from it. I can do this with UPDATE Items SET Clicks = Clicks + 1 WHERE Id = @Id; SELECT Name FROM Items WHERE Id = @Id This generates 2 plans/accesses to the table. Is possibile in T-SQL to modify the UPDATE statement in order to update and return the Name column with 1 plan/ac...

Iterate over a rowset, get a field which matches parameter, and then another field from that row

I have a stored procedure which has to get a password from my Users table. I am using a Username as a parameter. What is the best way to get the row where the Username field's contents match the Username parameter, and then the password (as this is the Username/password pair for one user). Cursors are one way to iterate over a rowset b...

deleting using left join and a table variable

Hi Folks, Maybe I'm missing a bracket or something but I'm having a hard time deleting rows from a table variable where I'm left joining and looking for the key I'm joining on. If it has a value, then I get rid of it. The problem is that I can't get the query to parse. Any ideas? declare @MrTemp ( key1 int ,key2 int ) insert i...