SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function

Different ways to insert the values from a stored procedure into a table. Let us quickly look at the conventional way of doing the same.

Please note that this only works with the stored procedure with only one resultset. Let us create a stored procedure that returns one resultset.

/* Create Stored Procedure */
CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO

Traditional Method:

/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSP
GO
/* SELECT from TempTable */
SELECT *
FROM #tempTable
GO
/* Clean up */
DROP TABLE #tempTable
GO

Alternate Method: Table Valued Function

/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(
MyDate SMALLDATETIME,
IntValue INT
)
AS
BEGIN
DECLARE @MyDate SMALLDATETIME
DECLARE @IntValue INT
INSERT INTO @retTestFn
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *
FROM dbo.TestFn()
GO

It is clear from the resultset that option 2, where I have converted stored procedures logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. Performance of the stored procedure is “usually” better than that of functions.

SQL Challenge

Here is a challenge that takes you away from those repetitive boring type of queries that you write over and over again, several times a day. All of us, the database people, are familiar with thinking in set based manner as well as row by row style. Here is something that is very interesting where you might need to process records in a 'three-line-at-a-time' fashion.

For the purpose of this challenge, imagine that you are working for a bank which just decided to scan all the banking documents. Assume that they have an old fashioned scanner which scans the documents and produces a text file with the customer number. So far so good. Well, not really! Unfortunately the scanner produces a graphical representation of the customer number using three lines of symbols: space, unerscores and pipe characters.

Here is an example of the output produced by the scanner.

Here are the rules to keep in mind while reading and recognizing the output generated by the scanner.

• Each digit is represented using 9 cells (3x3)
• Only spaces, underscores and pipe characters are used
• The number of digits in each account number may vary.
• The Scanner is not 100% reliable and it might produce some digits that are invalid

The Challenge

Your job is to read the output produced by the scanner and identify the the customer number represented by each image. Remember that the scanner is not very reliable and it might produce invalid digit representations. For each digit that is not valid, set the value to 'X'

Sample Data

Here is the sample data for this challenge. Please take care with spaces, tabs and carriage returns as each digit is represented by three lines of text and if a space, tab or carriage return is misplaced, the whole image will be distorted.

Id          ScanNumber

----------- ---------------------------

1            _  _  _  _  _  _  _  _  _  

            | || || || || |  || ||_ |_|  

            |_||_||_||_||_|  ||_| _| _| 

2               _  _  _  _  _  _     _ 

            |_||_|| || ||_   |  |  ||_ 

              | _||_||_||_|  |  |  | _|

3            _  _  _     _  _  _  _  _  

            |_ |_|| || ||_ |_| _|  ||_| 

            |_||_||_||_||_||_||_   | _| 

4               _  _  _  _  _  _     _ 

            |_||_|| ||_||_   |  |  ||_ 

              | _||_||_||_|  |  |  ||_|

5               _  _  _  _  _  _     _ 

            | ||_|| ||_||_   |  |  ||_ 

              | _||_||_||_|  |  |  ||_|

6            _     _  _     _  _  _  _ 

            | |  | _| _||_||_ |_   ||_|

            |_|  ||_  _|  | _||_|  ||_|

Expected Results

Based on the sample input and the rules discussed earlier, here is the expected output.

Id          Value

----------- ---------

1           000007059

2           490067715

3           680X68279

4           490867716

5           X90867716

6           012345678

Sample Scripts

Use the following script to generate the sample data for this challenge.

DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))

INSERT INTO @t

SELECT  1,--> 000 007 059

'_  _  _  _  _  _  _  _  _ 

| || || || || |  || ||_ |_|

|_||_||_||_||_|  ||_| _| _|

' UNION 

SELECT 2,-->  490 067 715

'   _  _  _  _  _  _     _ 

|_||_|| || ||_   |  |  ||_ 

  | _||_||_||_|  |  |  | _|

' UNION

SELECT  3, --> 680 X68 279

'_  _  _     _  _  _  _  _ 

|_ |_|| || ||_ |_| _|  ||_|

|_||_||_||_||_||_||_   | _|

' UNION

SELECT  4,--> 490 867 716

'   _  _  _  _  _  _     _ 

|_||_|| ||_||_   |  |  ||_ 

  | _||_||_||_|  |  |  ||_|

'  UNION

SELECT  5,--> X90 867 716

'   _  _  _  _  _  _     _ 

| ||_|| ||_||_   |  |  ||_ 

  | _||_||_||_|  |  |  ||_|

' 

UNION 

SELECT 6,--> 012 345 678

'_     _  _     _  _  _  _ 

| |  | _| _||_||_ |_   ||_|

|_|  ||_  _|  | _||_|  ||_|

Notes

1. Each record may have more than three lines of data (each line is separated by a CR and LF). Your code should consider only the first three lines.
2. The length of the first three lines of each recrd will always be the same and will be divisible by three.
3. There may be 3x3 blocks of spaces in the string. In such a case, you should generate an empty space in the output. If a 3x3 block does not create a valid digit (except for the case of a 3x3 block of spaces), you should generate an "X".
4. The number of 3x3 blocks in each record may vary

Single Marks Questions of University Exam Msc(IT)-1st

[1]. What are the four primary properties that most SELECT statements describe in a result set?

Most SELECT statements describe the following four primary properties of a result set:

• The columns to be included in the result set
• The tables from which the result set data is retrieved
• The conditions that the rows in the source table must meet to qualify for the result set
• The ordering sequence of the rows in the result set

[2]. What are the main clauses of a SELECT statement?

The main clauses of a SELECT statement can be summarized as follows:

SELECT select_list

[INTO new_table_name]

FROM table_list

[WHERE search_conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]

[ORDER BY order_list [ASC | DESC] ]

[3]. What are several keywords that you can use in a select list?

DISTINCT, TOP n, and AS

[4]. What type of objects can you specify in the FROM clause of a SELECT statement?

Tables, views, joins, and derived tables

[5]. What purpose does a join provide when used in a SELECT statement?

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table.

[6]. What are the differences between inner joins and outer joins?

Inner joins return rows only when there is at least one row from both tables that matches the join condition, eliminating the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause (as long as these rows meet any WHERE or HAVING search conditions).

[7]. What is a subquery?

A subquery is a SELECT statement that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery can be used anywhere an expression is allowed. A subquery is also called an inner query or inner select, while the statement containing a subquery is called an outer query or outer select.

[8]. What are the differences between a CUBE operator and a ROLLUP operator?

The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator. The differences between CUBE and ROLLUP are as follows:

• CUBE generates a result set showing aggregates for all combinations of values in the selected columns.
• ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.

[9]. For what types of columns can you not specify values in an INSERT statement?

Columns with an IDENTITY property, columns with a DEFAULT definition that uses the NEWID() function, and computed columns

[10]. What methods can you use to modify data in a SQL Server database?

The UPDATE statement, database APIs and cursors, and the UPDATETEXT statement

[11]. What are the major clauses contained in an UPDATE statement?

SET, WHERE, and FROM

[12]. Which statement should you use to delete all rows in a table without having the action logged?

The TRUNCATE TABLE statement

Using Transact-SQL on a SQL Server Database (Single Marks Question)

Using Transact-SQL on a SQL Server Database

[1]. In which window in Query Analyzer can you enter and execute Transact-SQL statements?

The Editor pane of the Query window

[2]. How do you execute Transact-SQL statements and scripts in Query Analyzer?

You can execute a complete script or an individual Transact-SQL statement by creating or opening the script in the Editor pane and then pressing F5. To perform this task, no other statements can be entered into the Editor pane. If there are other statements, you must highlight the script or statements that you want to execute, then press F5.

[3]. What type of information is displayed on the Execution Plan tab, the Trace tab, and the Statistics tab?

The Execution Plan tab displays a graphical representation of the execution plan that is used to execute the current query. The Trace tab, like the Execution Plan tab, can assist you with analyzing your queries. The Trace tab displays server trace information about the event class, subclass, integer data, text data, database ID, duration, start time, reads and writes, and CPU usage. The Statistics tab provides detailed information about client-side statistics for execution of the query.

[4]. Which tool in Query Analyzer enables you to control and monitor the execution of stored procedures?

Transact-SQL debugger

[5]. What is Transact-SQL?

Transact-SQL is a language that contains the commands used to administer instances of SQL Server; to create and manage all objects in an instance of SQL Server; and to insert, retrieve, modify, and delete data in SQL Server tables. Transact-SQL is an extension of the language defined in the SQL standards published by ISO and ANSI.

[6]. What are the three types of Transact-SQL statements that SQL Server supports?

DDL, DCL, and DML

[7]. What type of Transact-SQL statement is the CREATE TABLE statement?

DDL

[8]. What Transact-SQL element is an object in batches and scripts that can hold a data value?

Variable

[9]. Which Transact-SQL statements do you use to create, modify, and delete a user-defined function?

CREATE FUNCTION, ALTER FUNCTION, and DROP FUNCTION

[10]. What are control-of-flow language elements?

Control-of-flow language elements control the flow of execution of Transact-SQL statements, statement blocks, and stored procedures. These words can be used in Transact-SQL statements, batches, and stored procedures. Without control-of-flow language, separate Transact-SQL statements are performed sequentially, as they occur. Control-of-flow language elements permit statements to be connected, related to each other, and made interdependent by using programming-like constructs.

Control-of-flow keywords are useful when you need to direct Transact-SQL to take some kind of action. For example, use a BEGIN...END pair of statements when including more than one Transact-SQL statement in a logical block. Use an IF...ELSE pair of statements when a certain statement or block of statements needs to be executed IF some condition is met, and another statement or block of statements should be executed if that condition is not met (the ELSE condition).

[11]. What are some of the methods that SQL Server 2000 supports for executing Transact-SQL statements?

You can execute single statements, or you can execute the statements as a batch (a group of one or more Transact-SQL statements). You can also execute Transact-SQL statements through stored procedures and triggers. In addition, you can use scripts to execute Transact-SQL statements.

[12]. What are the differences among batches, stored procedures, and triggers?

A batch is a group of one or more Transact-SQL statements sent at one time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time. A stored procedure is a group of Transact-SQL statements that is compiled one time and can then be executed many times. A trigger is a special type of stored procedure that a user does not call directly. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.

Microsoft SQL Server 2000 (Single Marks questions of Msc(IT)-1)

[1]. What is SQL Server 2000?

SQL Server 2000 is an RDBMS that uses Transact-SQL to send requests between a client computer and a SQL Server 2000 computer. An RDBMS includes databases, the database engine, and the applications necessary to manage the data and the components of the RDBMS. The RDBMS organizes data into related rows and columns within the database.

[2]. What language is commonly used to work with data in a database?

SQL

[3]. What is XML?

XML is a standard format for data on the Internet. XML consists of tags within a text document that define the structure of the document. XML documents can be easily processed through HTML. Although most SQL statements return their results in a relational (tabular) result set, the SQL Server 2000 database component supports a FOR XML clause that causes the results to be returned as an XML document. SQL Server 2000 also supports XPath queries from Internet and intranet applications.

[4]. Which edition of SQL Server 2000 includes the complete SQL Server offering?

SQL Server 2000 Enterprise Edition

[5]. What is the purpose of the SQL Server 2000 relational database engine?

The SQL Server 2000 relational database engine is a modern, highly scalable engine for storing data. The database engine stores data in tables. Applications submit SQL statements to the database engine, which returns the results to the application in the form of a tabular result set. Internet applications submit either SQL statements or XPath queries to the database engine, which returns the results in the form of an XML document. The relational database engine provides support for common Microsoft data access interfaces, such as ADOs, OLE DB, and ODBC.

[6]. What SQL Server 2000 technology helps you build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources?

DTS

[7]. What are at least four administrative tasks that you can use the Enterprise Manager to perform?

Any four of the following tasks:

Defining groups of servers running SQL Server

Registering individual servers in a group

Configuring all SQL Server options for each registered server

Creating and administering all SQL Server databases, objects, logins, users, and permissions in each registered server

Defining and executing all SQL Server administrative tasks on each registered server

Designing and testing SQL statements, batches, and scripts interactively by invoking Query Analyzer

Invoking the various wizards defined for SQL Server

[8]. Which tool is commonly used to create queries and execute them against SQL Server databases?

Query Analyzer

[9]. What are at least five objects that can be included in a logical database?

Table,Data type,View,Stored procedure,Function,Index,Constraint,Rule,Default,Trigger

[10]. What are the major components involved in processing a SQL statement received from a SQL Server client?

The client, the tabular data stream, the server Net-Library, and SQL Server (the relational database engine)

SQL Server Interview Questions

Q.1 What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?
A. 1024

Q.2 how many system datatypes are in SQL Server.
A. 27

Q.3 What ODS API Stands For ?
A. Open Data Services Application Programming Interface

Q.4 What are the difference in MSSQL Server 2000 and its previous version 7.0?
A. there are many differences which can be seen in "whats new in SQL Server 2000" topic of the BOL available wid MSSQL Server 2000 installation , few major are given below

• OLAP services in 7.0 is now names SQL Server 2000 Analysis Services , Analysis Services also includes a new data mining component
• SQL Server 2000 introduces support for XML
• The programmability of Transact-SQL can be extended by creating your own Transact-SQL functions. A user-defined function can return either a scalar value or a table
• INSTEAD OF and AFTER Triggers
  INSTEAD OF triggers are executed instead of the triggering action (for example, INSERT, UPDATE, DELETE). They can also be defined on views, in which case they greatly extend the types of updates a view can support. AFTER triggers fire after the triggering action. SQL Server 2000 introduces the ability to specify which AFTER triggers fire first and last.
• Cascading Referential Integrity Constraints
  You can control the actions SQL Server 2000 takes when you attempt to update or delete a key to which existing foreign keys point. This is controlled by the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.
• Support for multiple instances
  SQL Server 2000 supports running multiple instances of the relational database engine on the same computer. Each computer can run one instance of the relational database engine from SQL Server version 6.5 or 7.0, along with one or more instances of the database engine from SQL Server 2000. Each instance has its own set of system and user databases
• Support for creating indexes on Computed Columns
• 64 GB Memory Support
  Microsoft SQL Server 2000 Enterprise Edition can use the Microsoft Windows 2000 Advanced Windows Extension (AWE) API to support up to 64 GB of physical memory (RAM) on a computer.

Q.5 How many dataype SQL Server 2000 Supports for date & time
A SQL server 2000 supports two datatypes for storing date and time :
datetime and smalldatetime

Q.6 How can you generate GUID in in Transact-SQL ?
A. GUIDs can be generated using the NEWID function.

Q.7 How many type of authentication method are there in SQL Server 2000
A. There are two type of authentication method in SQL Server 2000

Q.8 Whats the Difference between datetime and smalldatetime datatype in SQL Server 2000 .
A . The main difference between these two datatypes is in the amount of space they occupy. datetime occupies eight bytes and smalldatetime only four. The difference in size is due to a difference in precision. The precision of smalldatetime is one minute, and it covers dates from January 1, 1900 , through June 6, 2079 , which is usually more than enough. The precision of datetime is 3.33 ms, and it covers dates from January 1, 1753 , to December 31, 9999 .

Q.9. Can a user defined function return table ?
A. it is possible to design a user-defined function that returns a table.

Q.10. Whats is the nesting limit of Sql Server stored procedure.
A. SQL Server 2000 have a limit of 32 stored procedure nesting levels.

Q.11 How many type of triggers are there in Sql Server 2000.
A. There are two type of triggers
• After Triggers
• Instead of Triggers

Q.12 In how many ways you can recieve information from stored procedure
A. there are 4 ways to receive information from a stored procedure:
Resultset , Parameters , Return value , A global cursor that can be referenced outside the stored procedure.

Q.13 What are the limits of Sql Server 2000.
A.When you are creating or changing a stored procedure,please keep in mind that
The name of the procedure is a standard Transact-SQL identifier. The maximum length of any identifier is 128 characters.
Stored procedures may contain up to 1,024 input and output parameters.
The body of the stored procedure consists of one or more
Transact-SQL statements. The maximum size of the body of the stored procedure is 128MB.

Q.14 Whats the limitation of user defined funtion ?
A User-defined functions have one serious limitation. They cannot have side effects. A function side effect is any permanent change to resources (such as tables) that have a scope outside of the function (such as a non-temporary table that is not declared in the function). Basically, this requirement means that a function should return a value while changing nothing in the database. it means in short that "user defined function can not use UPDATE / DELETE on permament table objects in MSSQL"

Q.15 what is @@Fetch_status ?
A. @@fetch_status is a function (or global variable) that returns the success code of the last Fetch statement executed during the current connection. It is often used as an exit criterion in loops that fetch records from a cursor.

Q.16 How you can trap error in Sql Server 2000
A. by using @@error .
After each Transact-SQL statement, the server sets the variable to an integer value:
0—if the statement was successful
Error number—if the statement has failed

Q.17 How many type of Contraints are in MSSQL.
A. SQL Server 2000 supports five classes of constraints.
1) NOT NULL
2) CHECK
3) UNIQUE
4) PRIMARY KEY
5) FOREIGN KEY

Q.18 How you can get the last identity value inserted in any table ?
A. SQL Server 2000 has a System Variable @@IDENTITY which gives the last identity element value inserted in any table

Q.19 How many type of indexes are there ?
A. there are two type of indexes Clustered and Non-Clustured

Q.20 How many Index can be created on a table
A. 249 Non Clustured and 1 Clustered index and 5 Reserved for future Use.