Feeds:
Posts
Comments

Posts Tagged ‘process’

You can passs temp table inside a dynamic sql and it will process that for you


declare @s varchar(200)

if object_id('tempdb..#t') is not null 
drop table #t

Select 1 as n
into #t

select * from #t -- first time 

set @s = 
	'update #t 
	set n = 2

	insert into #t
	values (3)
	'

exec (@s)

select * from #t -- seccond time

 

Read Full Post »

Command like Sql can be used to run sql scripts saved in disks. Therefroe if there is a bunch of scripts

(files) they can all be added with ttheir path and file name in notepad and have it saved with anyname with a

cmd extension. Then running this file would execute all the lines in the file.

Use “sqlcmd” instead of osql because “osql” cannot handle alot of special characters like the “£” sign.

Here is an example:
sqlcmd -S MyServerName -E -d My_dbName -i “C:\MyTSQL\MyScript.sql”

The above if run in commandline will execute the script in the path.

This is usefull when we need to run batches of scripts.

To make scripts upload/commit to a server by using the above command make sure the SP/Function or other

objects are alwasy dropped and recreated e.i. starts with the create statement as below:

--For SP:
---------
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]]
GO

CREATE PROCEDURE [dbo].[mySP] @var AS VARCHAR(100)
as 
begin 
	...
end 


--For Function:
--------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myUDF]') AND type in (N'FN', N'IF', 

N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[myUDF]
GO


create FUNCTION [dbo].[myUDF]
(
	@var AS VARCHAR(100)
)
RETURNS INT
AS 
BEGIN
	...
end 

Read Full Post »