tsql

Variable as numeric sent to stored procedure (SQL Server 2005)

I see that with SQL Server 2005 you can pass a parameter as numeric e.g. create procedure dbo.TestSP @Param1 numeric as But what does this equate to? E.g. Numeric(10,0), Numeric(9,2), etc? We have some Developers here who are using this instead of the correct definition for the field that this parameter is going to be used agains...

Getting XQuery [modify()]: Top-level attribute nodes are not supported error while modifying xml

DECLARE @mycur CURSOR DECLARE @id int DECLARE @ParentNodeName varchar(max) DECLARE @NodeName varchar(max) DECLARE @NodeText varchar(max) SET @mycur = CURSOR FOR SELECT * FROM @temp OPEN @mycur FETCH NEXT FROM @mycur INTO @id,@ParentNodeName,@NodeName,@NodeText WHILE @@FETCH_STATUS = 0 BEGIN PRINT @id -- sample statements PRINT @Pare...

Using Parameter Values In SQL Statement

I am trying to write a database script (SQL Server 2008) which will copy information from database tables on one server to corresponding tables in another database on a different server. I have read that the correct way to do this is to use a sql statement in a format similar to the following: INSERT INTO <linked_server>.<database>.<ow...

Why would you Truncate immediately before Dropping a temp table?

I see some code where the author has truncated a temp table immediately before dropping the temp table. Is there a reason for doing this? TRUNCATE TABLE #Temp DROP TABLE #Temp ...

SQL Server 2008 Problem with SCOPE_IDENTITY()

My code does not update the thread field. It is null. Anyone have any ideas? INSERT INTO [Messages]([Sender], [Receiver], [Job_Number], [Subject], [MessageText], [DateSent]) VALUES(@Sender, @Receiver, @Job_Number, @Subject, @MessageText, @DateSent) SET @ThreadID = SCOPE_IDENTITY() UPDATE [Messages] SET Thread = @ThreadID WHERE Messag...

Question About DateCreated and DateModified Columns - SQL Server

CREATE TABLE Customer ( customerID int identity (500,20) CONSTRAINT . . dateCreated datetime DEFAULT GetDate() NOT NULL, dateModified datetime DEFAULT GetDate() NOT NULL ); When i insert a record, dateCreated and dateModified gets set to default date/time. When i update/modify the r...

How to drop all stored procedures at once in SQL Server database?

Currently we use separate a drop statements for each stored procedure in the script file: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MySP]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[MySP] Is there a way to drop them all at once, or maybe in a loop? ...

Calling stored procedure from another stored procedure and returning result as new columns

How to call stored procedure from another stored procedure and return the result as first one's column? ALTER PROCEDURE [dbo].[GetItems] AS SET NOCOUNT ON SELECT ID, AddedDate, Title,Description, Result of Stored Procedure "CountAll" call with parameter ID as Total FROM dbo.Table1 And also: how to be if CountAll stored proced...

Generating the SQL query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatte...

How can I update many rows with SQL in a single table?

Hi folks. I have a table and one of the columns holds web addresses like: 'http://...' or 'https://...'. The problem is that there are some invalid entries, like 'shttp://...' or '#http//...' (the first character is invalid) and I want to correct all of them. I use the following SQL statement: 'SELECT [...] FROM MyTable WHERE WebAdd...

Filling the missing gaps(Sql Server 2005) [Set based using recursive CTE]

I have a table MissingData 1 10 NULL NULL 22 NULL The desired output will be MissingData 1 10 10 10 22 22 i.e. the nulls will be filled up by the previous value until a new value is appearing. I can solve this by using loop but my requirement is to solve it by CTE in which I am not so comfortable as of now. Thanks ...

SQLCLR and DateTime2

Using SQL Server 2008, Visual Studio 2005, .net 2.0 with SP2 (has support for new SQL Server 2008 data types). I'm trying to write an SQLCLR function that takes a DateTime2 as input and returns another DateTime2. e.g. : using System; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; namespace MyCompany.SQLCLR { public ...

Update column using rows of a table, without using cursor or any other iteration.

Update Combined column using CategoryCode of every OrderId. In this example there are two OrderIds 990 and 986. Need to concatenate categories of these two individually. The desired result is like this. 990 Bus, Pub, Shoot, Club, Bus, Hos Thanks. ...

view to select specific period or latest when null

Hi I have a product table which simplifies to this: create table product(id int primary key identity, productid int, year int, quarter int, price money) and some sample data: insert into product select 11, 2010, 1, 1.11 insert into product select 11, 2010, 2, 2.11 insert into product select 11, 2010, 3, 3.11 insert into product sele...

Sql select rows containing part of string.

Hello! I want to write a comparation procedure (t-sql) for site seo. I have a table with field 'url' (nvarchar()) that contain a part of site url's. Ex: 'mysyte.com/?id=2'. Also this table for each url contains metadata, that i need to extract. The main problem is that full url on site looks like 'mysyte.com/?id=2&region=0&page=1', and...

Sanity Check - Concatenating Date Values - SQL Injection

We currently receive parameters of values as VARCHAR's, and then build a date from them. I am wanting to confirm that the method below would stop the possibility of SQL injection from this statement: select CONVERT(datetime, '2010' + '-' + '02' + '-' + '21' + ' ' + '15:11:38.990') Another note is that the actual parameters being passe...

How can I iterate over a recordset within a stored procedure?

I need to iterate over a recordset from a stored procedure and execute another stored procedure using each fields as arguments. I can't complete this iteration in the code. I have found samples on the internets, but they all seem to deal with a counter. I'm not sure if my problem involved a counter. I need the T-SQL equivalent of a forea...

Why decimal behave differently?

Hello, I am doing this small exercise. declare @No decimal(38,5); set @No=12345678910111213.14151; select @No*1000/1000,@No/1000*1000,@No; Results are: 12345678910111213.141510 12345678910111213.141000 12345678910111213.14151 Why are the results of first 2 selects different when mathematically it should be same? ...

t-sql most efficient row to column? crosstab for xml path, pivot

I am looking for the most performant way to turn rows into columns. I have a requirement to output the contents of the db (not actual schema below, but concept is similar) in both fixed width and delimited formats. The below FOR XML PATH query gives me the result I want, but when dealing with anything other than small amounts of data, ...

How do I convert an arbitrary list of strings to a SQL rowset?

I have a simple list of strings, which may be of arbitrary length. I'd like to be able to use this list of strings as I would use a rowset. The app in question is running against SQL Server. To be clearer, if I do SELECT 'foo', 'bar', 'baz' I get 'foo', 'bar', and 'baz' as fields in a single row. I'd like to see each one of them as a...