Custom key board mapping in Sql Server Management Studio to get the results of any table

Here is a simple trick to get the results of any table with custom key mapping in SSMS. With this we can get rid of writing the different Select queries to get the records and can save few key strokes. Below 3 steps will do the trick.

step 1:  Create a stored procedure which accepts the table name, no of rows to be returned and the order of the results

1: CREATE PROCEDURE [dbo].[GetAnyTableRowsInSSMS]
2: (
3: @TableName VARCHAR(20),
4: –No of rows to be returned in the result set
5: @NoOfRows SMALLINT=5,
6: –‘a’ for ascending, ‘d’ for descending
7: @Order VARCHAR(4) =’a’
8: )
9: AS
10: BEGIN
11: IF @Order=’a’
12: SET @Order =’asc’
13: ELSE
14: SET @Order=’desc’;
15: DECLARE @ResultQry NVARCHAR(100);
16: SET @ResultQry= ‘SELECT TOP ‘ + CAST (@NoOfRows AS VARCHAR(5))+ ‘ * FROM ‘ + @TableName + ‘ ORDER BY 1 ‘ + @Order
17: EXEC SP_ExecuteSql @ResultQry
18: END

Step 2: Bind this stored procedure to SSMS custom key as in the below screenshot

Step 3: Execute in the query analyzer with the required parameters

Here we got top 2 rows from user table in ascending order. Like wise by pressing ctrl+5 on “users” will get the top 5 results in ascending order as we have given no of rows as 5 and order as ascending by default.

Points to Ponder:

1. Place this stored procedure in some test database rather the original database to avoid accidental copy into production.
2.  The results will be sorted by considering the first column the table. You can change this by passing the column name also as the parameter to the Stored Procedure.
Advertisements

Getting Top 1 and Bottom 1 results with a simple SQL query

Whenever there is a requirement to get a top 1 and the bottom 1 records in a single query, we start writing the query as below

SELECT TOP 1 * FROM Customers order by CustomerID  ASC
UNION ALL
SELECT TOP 1 * FROM Customers order by CustomerID DESC

But this will result below error.

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword ‘UNION’

This will be resolved simply by making use of CTEs which are introduced in SQL2005 and higher

;WITH TopBottomRows
AS
(
SELECT TOP 1 * FROM Customers order by CustomerID  ASC
UNION ALL
SELECT TOP 1 * FROM Customers order by CustomerID DESC
)
SELECT * FROM TopBottomRows

Hope this helps..

Duh..Its been a long time

Yes..Its been a long time (very very long ) since I posted my blog entries here..I was busy with my job and other activities and hence couldn’t able to visit this space more frequently..I would have written at least one post in a month but it wasn’t happened. From now I will post more technical articles and try to put useful content over here. Keep watching this space..

Happy Coding….

SQL Server FAQ

Here are some mostly useful FAQs on SQL Server
==============================
1. For finding the Nth salary
 
Method 1:
declare @sal int
set @sal=(select top 1 sal from emp where empid not in (select top 0 empid from emp order by
sal desc) order by sal desc)
print @sal
select * from emp where sal=@sal
         This will give u the top most salary
Method 2:
 select top 1 sal  from (select top 1 * from emp order by sal desc) sal
order by sal
Method 3: [Better One]
Select * From Emp X Where 5 =
(
Select Count(Distinct Sal) From Emp Where sal >=X.sal
)
 

Calling JavaScript from Asp.net

Most of we think that it is very difficult to call a javascript code from
codebehind file in asp.net. But it is possible with a single line of code….
 
Let me explain to display a confirmation dialog box in a page…
 
If we have a button named Button1 then
  in Page_Load  add the following single statement
 
    Button1.attributes.add("onclick","if (confirm(‘Are you sure to proceed?’)) return true;else return false;")
       That’s it. Our job is done..
 
Now we can write the server side coding in Button1_Click that shud be executed if user clicks "yes"
      on the prompt. It won’t do the postback if we go with "No" option
 
=========
Happy  asp.net
=========