A number of examples are given at Wikipedia. A somewhat recent example is the Quebec independence referendum of 1995. Independence was rejected by a narrow margin, with 50.58% voting against. Quoting from the Wikipedia article:
The day after the referendum, Jacques Parizeau resigned as the leader of the Parti Québécois, as he had said he would do in an interview with TVA taped days before the referendum but not made public until after the vote. Lucien Bouchard was the only candidate to succeed him. Bouchard became Premier on January 29, 1996. Over the course of the next few years, support for sovereignty decreased. Despite winning reelection in 1998, the PQ chose not to hold another referendum, waiting for "winning conditions". The PQ would lose the 2003 provincial election to the Liberal Party of Quebec, led by Jean Charest.
In the decade after the failed referendum, Parti Québécois dropped from 45% to 28% of the vote. Whether this is a consequence of the failed referendum, I don't know, but I guess it might be, and I wouldn't be surprised if a failed independence referendum in Scotland (or, for that matter, Catalonia) would have similar consequences.
Edit, 3½ years later:
In the Scottish independence referendum, 2014, 55.30% of the voters rejected Scottish independence. In only four local authorities (including Dundee and Glasgow) did a majority support independence.
In the United Kingdom parliamentary election, 2015, the SNP saw its share of the Scottish vote increase from 20% to 50%, leading to an increase from 6 to 56 seats (out of 59 Scottish seats) in the first-past-the-post system.
In the Scottish Parliament election, 2016, SNP saw its constituency vote increase slightly (from 45.4% to 46.5%) and its regional vote decrease slightly (from 44.0% to 41.7%), leading to a decrease from 69 to 63 seats (out of 129) in the additional member system.
Membership increased from 25,642 in September 2014 to 116,000 in April 2016.
Of course, other things have happened in the meantime and it is difficult to attribute trends to any single event. However, it is apparent that the lost independence referendum did not lead to a decrease in SNP popularity (thus far).