How to use IN Clause with parameter in MS SQL

 

You might be wondering how to pass the value as parameter in IN clause. It is a kind of tricky one. If you directly execute your select query by passing the parameter your query will through error.

Here we will see how it can be done,

First we will create the table

CREATE TABLE [dbo].[EMP](

    [ID] [int] NULL,

    [Name] [nchar](10) NULL

) ON [PRIMARY]

GO

Now we will insert some values to the table

INSERT INTO [Test].[dbo].[T] ([ID],[Name]) VALUES(1,'Mike')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(2,'John')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(3,'Russel')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(4,'Thomas')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(5,'David')

GO

Finally we will query the table using IN. I want all the Employee with the ID in 3,4,5 and our query for that should be

select * from t

Declare @id varchar(20)

set @id= '3,4,5'

exec('SELECT * FROM EMP WHERE ID IN('+@id+')')

Above query was useful if you wanted to use INT or any other numeric data type in your IN clause. What you will do if you wanted to use it for char data type?

Yes we will see that as well here.

Query for Char data type should be,

DECLARE @id VARCHAR(MAX)

set @id= 'Mike,John,David'

SELECT @id = '''' + REPLACE(@id,',',''',''') + ''''

EXEC('SELECT * FROM EMP WHERE Name IN('+@id+')')

GO

Result after executing above query for Int data type,

Result after executing above query for CHAR data type,

How to call Stored Procedure from Trigger in MS SQL

 

Another interesting Interview question “Can we call Stored procedure form Trigger in MS SQL”? What will be your answer YES or NO? Answer should always be “YES”.

You can call Stored Procedure from trigger. In this article we will go through a simple After Insert trigger as an example.

create trigger trg_Ins_CustTrigger on Customer

After Insert 

AS

Declare @Name as varchar(100)

Declare @Address as varchar(100)

select @Name=Name, @Address=Address from inserted

exec CustDet @Name, @Address

I believe above code is self explanatory as the table and field names I have used is a clear English text.

If you have any question please post it in comment section.

How to make Marquee in ASP.NET web page and dynamically assign the value to it.

 

When you make a web application it is always good to make a colourful user interface.  One of the most attractive feature in HTML is Marquee. Today we will see how to use marquee in ASP.NET web application.

Here we will use label to assign the text which moves inside the marquee tag.

We will see the code now,

<div> 

<marquee direction="right" scrollamount="2" loop="true" width="100%" bgcolor="#ffffff" >

    <asp:Label id="lblMarquee" runat="server" ForeColor="ForestGreen" Font-Bold="True" ></asp:Label>

</marquee>

</div>

Here what we are doing is we kept the label inside marquee tag so label will move based on the marquee settings.

Now if you assign any value from your code behind to this label it will move or rotate based on the marquee settings.