User-Defined Functions in SQL Server

In SQL Server user can define function to return a table or some value. Following are two ways of creating function:

1- Returning Table

-----------------Returing table

create function printTwo

(@projectId varchar, @resourceId varchar)

returns table as

return

(

select @projectId as [proj],@resourceId as [resc]

);

2- Returning Scalar(single) value

-----------------Returing scalar

create function printOne

(

@value int

)

returns int

WITH EXECUTE AS CALLER 

as

begin

return @value

end 

3- Checking the output by calling in different ways 

select dbo.printOne(1) 'Result';

select * from dbo.printTwo('1','2');

select dbo.printOne(1) 'Result', * from dbo.printTwo('a','b');

Export sql server query output to CSV - via BCP utility

To export SQL query data to a comma separated value (CSV) file we can use Bulk Copy Program (bcp) utility. Follow below steps to get the desired CSV file.

1. Enable xp_cmdshell property to use the bcp utility 
   
2. Write the desired query and passed it in the xp_cmdshell command 
   

    3. Output CSV

HTTP Request Via SQL Server Query

Hello again, in this post we will see how to perform an Hypertext transfer protocol (HTTP) request through SQL server query. However remember that this action is not a general practice, it should be performed for specific cases like for instances:

• Data to be posted to some system at day end or specific time interval
• XML or JSON based data needs to be retrieved from integrated systems
• In a case where security is not a major concern (like internal systems)

This method is supposed to be less efficient in terms of execution time as compared to the traditional web request done by the web applications. So I would recommend to use this only if you have a specific requirement like ones mentioned above or if you do not have any other way then this.

Following are steps to create HTTP request via SQL query:

1. First check if  OLE (Object Linking and Embedding) Automation Procedures are enabled from the following built-in stored procedure:

           EXEC sp_configure 'Ole Automation Procedures'; 

           The config_value and run_value should be 1

     2. If not enabled already then execute another built-in SQL SP of sp_configure:

           exec sp_configure 'show advanced options', 1;  

           GO  

           RECONFIGURE;  

           GO  

           exec sp_configure 'Ole Automation Procedures', 1;  

           GO  

           RECONFIGURE;  

           GO  

    3. Now to perform a simple request consider the below query where data is retrieved in JSON format:

DECLARE @obj int,

        @url VarChar(MAX),

        @response VarChar(8000),

        @requestHeader VarChar(MAX),

        @requestBody VarChar(MAX),

@status int

SET @url = 'http://65.181.112.199:88/default.aspx?source=CategoryCombo'

EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

EXEC sp_OAMethod @obj, 'Open', NULL, 'GET', @url, 'false'

Exec sp_OAMethod @obj, 'Send'

EXEC sp_OAGetProperty @obj, 'responseText', @response OUT

Exec sp_OAMethod @obj, 'status', @status OUTPUT

EXEC sp_OADestroy @obj

SELECT @response [RESPONSE], @status [Status]

   4. Result:

For more information visit stack overflow:

http://stackoverflow.com/questions/19307704/how-read-json-data-from-url-in-sql-server

SQL Server Stored Procedure with input parameter

Hello. Hope you are doing good. In this post we will learn how to create an SQL stored procedure with input parameter.

Consider following steps:

1. Use create procedure command to create procedure like "create procedure abc"
2. Define input parameter with @ to denote parameter values like '@Name varchar(15)', note that @Name is a parameter of type varchar with 15 character length. You can add multiple parameters by separating each with a comma (,)
3. Write the SQL command you want the procedure to execute. For this example we will print name with today's date:

     create procedure showDateWithName 

     @Name varchar(15)

     as

     begin

     select 'Hello '+@Name +'! today is '+ convert(varchar,GETDATE(),106);

     end

• To use the stored procedure run the execute command:

       execute showDateWithName 'Hasan'

Procedure Result